One feature in web design and data tools that I find really helpful is the when you hover your mouse cursor over something and after a brief pause a little with additional information comes up. This could be a description of a button, an extended snippet of the text we are viewing or the full link address of a hyperlink. In most computer programs we have a similar feature when we hover over a button and in Excel we can add a comment into any cell with as much text as we want and even a picture (although that would blow up the file size).
I was thinking about this today as I considered one of my regular problems in Excel, which is that when I am on one particular worksheet, I cannot easily see related information on another worksheet without switching to it and finding the relevant cell. As an example, I may have current year sales laid out in one worksheet and last year sales on another. If I have both worksheets in the same format, I can ensure that I select the same cell in each, but I still have to flick between tabs to make comparisons. I came up with a solution for this a while ago where I had a macro that would display a message box showing all the related figures for any cell I selected, from any of the worksheets in that file. While it does work, the shortfall is that to view the results for another cell, I have to close or cancel the message box and click the macro button again, using up precious seconds (okay, the time doesn’t really matter, but it is tedious to do that over and over), which brought me to the line of thinking I found myself on today, mouse over events like tooltips might be the answer.
I’ll admit that for a little while I forgot all about comments in cells and I was considering using transparent labels, although I don’t actually know how to do something like that and would have to learn and practice until I got it right. Eventually I DID remember cell comments and from there all I had to do was figure out a nice way to populate them using VBA.
The first thing is that I didn’t want the comments to always be there, because the numbers are going to change, it would look a bit untidy with all those little red triangles and I fear for Excel’s processing abilities and file sizes quite often with the reports I work on. Luckily, it didn’t take long to devise a macro that would add comments and another that would remove them.
Nice easy code here! Then I needed to figure out how to do multiple lines so that I don’t just have one long line of information, but that was also quite easy.
ActiveCell.AddComment("Line 1" & Chr(10) & "Line 2")
And then finally I want to bring back cell data into the comments, and end up with the final code to add a comment to a cell (starting from Sheet1 as the main one).
Dim sheet2info As String Dim sheet3info As String With Selection sheet2info = Sheets("Sheet2").Range(.Address).Value sheet3info = Sheets("Sheet3").Range(.Address).Value ActiveCell.AddComment ("Sheet 2: " & sheet2info & Chr(10) & "Sheet 3: " & sheet3info) End With
That’s it really, the other part to remove the comments doesn’t need much adjusting.
Now I just need to adapt this macro to be run from a loop over a range, to cover the cells I want it to automatically each time a button is pressed (I might call it the X-ray button). Looking at the end result now, I can imagine that in the future it might be better to dump all the relevant figures into arrays, then create comments in arrays and finally add the comment arrays to my ranges, but for right now I think I can leave it as it is. I’ll try it out and update on here if it was a really bad idea!
Thanks for reading and I hope I helped someone along on their VBA journey, and gave others some interesting ideas for dealing with weird Excel problems!