Create a Floating Key/Legend

[GhostBroker]

Hello Forum Members,
I'm trying to create a floating key/legend for a spreadsheet. At present, the key exists in cells T1:T10. Panes are frozen from rows 1:7. The floating key will free up a lot of visual real estate, and show the user how to decipher the colors of cells. So basically, I need the key to float even as the spreadsheet is scrolled. I also need the user to be able to move the floating key to different positions on the screen, in the event that the key is covering needed information. The floating key does not need to be able to update, just to show the colors and their meanings. I've done some online research, and it seems like other users are doing things that are more complex than this, but I haven't found this particular solution.

Any advice/direction is appreciated.
Thank you.

Very Respectfully,

GhostBroker

 

[lrobbo314]

You could create a userform in the VBA editor, add the information that you want on the floating window, then add a bit of code that would launch the userform when the workbook is opened or when the user selects the page that you want it on.

 

[Logit]

Paste in ThisWorkbook :

Option Explicit

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

Create a UserForm and paste this in the form's code window :

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then Cancel = True
End Sub

 

[GhostBroker]

Thank you team. Please forgive my lack of experience, but...
I've gotten to the UserForm part (see attachment 1) how do I attach the Key (see attachment 2) to this UserForm?

 

[lrobbo314]

When you open the userform in the VBA editor it should bring up a toolbox. If not, you can make it visible under the view menu. Then you can just add labels to it and make the labels match the text and colors in your image.

You will want to bring up the properties of the userform as well and change the ShowModal property to false to allow the user to scroll through the worksheet while the form is visible.

 

[Logit]

lrobbo314​

Thanks for providing GhostBroker the hints.

 

[GhostBroker]

Logit & lrobbo,

Thank you guiding me through this one!

You guys are great!

Very Respectfully,

GhostBroker

 

[Logit]

You are welcome.