It's no secret - I'm hopeless with Excel formulae

  Forum Editor 20:55 02 Apr 2009
Locked

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?

  Zak 21:28 02 Apr 2009

Insert Comment for each coloured cell possibly?

  MAJ 21:36 02 Apr 2009

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.

  Zak 22:06 02 Apr 2009
  MAJ 22:12 02 Apr 2009

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.

  Forum Editor 23:43 02 Apr 2009

Perhaps I'll wait a while to see if anyone else comes up with a solution before I tick the thread.

  VoG II 09:12 03 Apr 2009

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.

  Forum Editor 20:24 03 Apr 2009

Wonderful - working like a dream. As ever, many thanks.

  Forum Editor 20:26 03 Apr 2009

As a bonus for me, your suggestion suits one of my own files perfectly, and we set it up today.

This has indeed been a lucky thread for me, and thank you to to everyone who responded - what a helpful lot you truly are.

  MAJ 21:30 03 Apr 2009

Told you that VoG™ would have an ingenious method of doing it, FE, but I'm glad I helped a little as well.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Is this the future of VR and AR?

Best iPad buying guide 2017

Comment regarder le Bureau des Légendes en ligne ?