VBA Code to create hover-over message:

[Chad Bateman]

Hello All,
I'm trying to figure out how I can build some VBA code that will automatically add hover-over to specific text in a specific column. For example, for every cell in column C, if the number "20" exists, add a hover-message (pop-up box? Not sure what it's called) that would say "California", every time number "30" exists add a hover-message that says "New York", etc.

If someone can just point me to the framework for this I can probably figure out how to iron out the details.

Thanks a ton!

[skywriter]

It's called a cell comment. If you google it you will get lots of websites with sample code, some videos etc. Go into the help search box in the visual basic editor and search cell comment and you'll get a lot of help there also.

Here's a sample:

Excel Developer Reference
Comment Object
Represents a cell comment.
Remarks


The Comment object is a member of the Comments collection.


Example


Use the Comment property to return a Comment object. The following example changes the text in the comment in cell E5.

Visual Basic for Applications
Worksheets(1).Range("E5").Comment.Text "reviewed on " & Date

Use Comments(index), where index is the comment number, to return a single comment from the Comments collection. The following example hides comment two on worksheet one.

Visual Basic for Applications
Worksheets(1).Comments(2).Visible = False

Use the AddComment method to add a comment to a range. The following example adds a comment to cell E5 on worksheet one.

Visual Basic for Applications
With Worksheets(1).Range("e5").AddComment
.Visible = False
.Text "reviewed on " & Date
End With

Good Luck!!!

[LJMetzger]

Hi Chad,

Actually it's called 'Screen Tip Text' (analogous to 'Control Tip Text' on UserForm controls). It is not a COMMENT. It is created by putting a 'Dummy Hyperlink' in each cell that has an appropriate value.

You have to modify Sub GetScreenTipTextValue() [in blue below] to assign the correct text values to numbers.

See the attached sample working file and the code that follows:

Sheet Code:

Please Login or Register  to view this content.

Ordinary Module Code such as 'Module1':

Please Login or Register  to view this content.

Lewis

[skywriter]

So you can't add a comment to a cell based on a value in that cell and change that comment based on the worksheet change event?
I have some comments that I've added through code and although they weren't based on values in the cells they didn't require very much code. They also come up when I hover over the cell they are in, hence the reason for my apparent stupidity by saying it was a COMMENT.

[LJMetzger]

Hi skywriter,

So you can't add a comment to a cell based on a value in that cell and change that comment based on the worksheet change event?.

Yes you can. Adding comments is a perfectly valid way to solve the problem. Hyperlinks seemed like a superior method to me for this application because:
a. Comments have to be formatted to remove the extra whitespace.
b. It is relatively easy to accidentally change a comment, make a comment visible all the time, or to delete a comment.

Just my opinion.

Lewis

[Jenn68]

Hi Lewis,
I'm resurrecting this thread if I may. What you've managed to do is exactly what I'm looking to do with my worksheet. I've analyzed your code and played with your sample workbook in an effort to figure out how to apply it to my application. I hope you don't mind me reaching out for some help.

On my worksheet (ws_gui)
Range("I13:I19") all contain the values "+"
Range("J13:J19") all contain the values "~"
Range("K13:K19") all contain the values "-"

What I am trying to accomplish to to display the appropriate "texttip" when the user hovers over one of those cells. The user will be able to then click on a cell to execute some code. The 'screentiptext' value will be different depending on the row of the cell. With the code below that I borrowed from you and attempted to adapt (really all I did was change the constant range reference and it's variable name, I'm not getting anything happening so clearly I am missing something in my adaptation. Note that the worksheet is protected but these ranges are not locked.

Here is my code from a module within my workbook.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.