and I need one urgently - I wonder if anyone can help?
I have a friend who runs a spreadsheet that she uses to score university lecturers on their course presentations. She uses coloured cells to do this, and although she knows which colour means what, her colleagues don't. She wants a pop-up that contains a key to the cell colours. She wants it to float, so that it's visible as people move around the worksheet.
Does that make any sense, and if so, can anyone tell me how it's done?
I'm sure VoG will have some ingenious method to do it, FE, but if the spreadsheet isn't more than one screen in width, your friend could create a legend on the first row then freeze that first row. The first row would then remain visible while the sheet is being scrolled up and down.
Highlight the second row and go to Window > Freeze Panes.
Or, further to my post above. Create the legend in the first row, then click in the cell below and to the right of the last cell of the legend and go to Window > Freeze Panes. That will leave the legend visible no matter where the user scrolls.
MAJ's Freeze Panes suggestion seems pretty good to me.
There isn't a mouseover event in Excel, nor is it easy to create floating windows without some fiendish programming.
Here's another suggestion: right click the sheet tab, select View Code and paste in
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim msg As String Select Case Target.Interior.ColorIndex Case 3: msg = "red" Case 10: msg = "green" Case 11: msg = "blue" Case 6: msg = "yellow" Case 13: msg = "purple" End Select If msg <> "" Then Cancel = True MsgBox msg, vbInformation End If End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True MsgBox Target.Interior.ColorIndex End Sub
Now, right clicking a coloured cell will pop-up a message.
You can substitute the keywords instead of the colour names that I've used. You can also add additional colour numbers with keys inside the Select Case statement.
Of course, you'll need to know which colour number corresponds with each colour (e.g. 3 is red). You'll find a list if you click here and scroll down a bit. Alternatively double-click in a coloured cell.