Solved

Create a tooltip displaying the contents of a cell in VBA for Excel 2003

Posted on 2012-04-10
6
2,286 Views
Last Modified: 2012-04-18
Hi everybody,

In a worksheet, I have a column that reads and displays the content of cells from another worksheet. As my column is too narrow to display the whole information, I wrote a VBA macro that would display that actual contents when right-clicking the cell and selecting the macro in the shortcut menu.

However I was wondering whether I could create in VBA a tooltip that would do the same just when hovering around the cell using the mouse.

Any idea for me to get started?

Many thanks in advance for any clue.

Cheers.

PG
0
Comment
Question by:pg111
  • 3
  • 3
6 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37831322
You know if you put comments in the cells, then when you hover, those comments popup.  That is the simplest solution.

Hover functionality chews up CPU cycles, but it is possible.  Here's a solution I worked on about a year ago, which displays comments in status bar, adds comments to the hover field, and even pops up a balloon (looks like a userform) with info.  Download the sample and check it out:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26763937.html

Here's one Sid worked on at the same time that pushes comments into the cells:

http://www.experts-exchange.com/Microsoft/Applications/Q_26777993.html

Hopefully from these two solution examples you can get some ideas about how you want to approach your problem, and some code to go with them.

Dave
0
 

Author Comment

by:pg111
ID: 37831331
Thanks, dlmille.

I will think it over and make sure to let you know of the outcome.

Cheers.

PG
0
 

Author Comment

by:pg111
ID: 37863773
Hi Dave,

Thanks again for your suggestion. I apologize for not being able to test it earlier but I've been busy those past few days. Now I'm reverting to you as promised.

Actually my problem seems simpler than in the example you referred me to.

I'm having a worksheet in which range E2:E14 displays jobs' names from other worksheets in the same workbook. So the value for each cell in that range is dynamically updated by a formula (e.g. =IF(Proj_Trad1_nom<>"";Proj_Trad1_nom;"").

As my column E is too narrow  to display the full contents of the current cell, further your suggested approach, I would like a comment to be automatically generated when loading the worksheet that reads the value for each cell in the range E2:E14 and populates a comment with it I could display.

I tweaked the code from Sid as flws:

Dim strComment As String

Private Sub Worksheet_Activate()
    
If Intersect(Range("E2"), Range("E14")) Is Nothing Then
    Application.EnableEvents = False
    
    For i = 2 To 14
        If Range("E" & i).Value <> "" Then
            strComment = Range("E" & i).Value
            If Len(Trim(strComment)) = 0 Then
                Range("E2:E14").ClearComments
            Else
                With Range("E" & i)
                    .ClearComments
                    .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=strComment
                End With
                strComment = ""
            End If
        End If
    Next i
    
    Application.EnableEvents = True
End If

End Sub

Open in new window


So it works just fine by using the Activate event i/o Change for the current worksheet.

Thanks a lot for your help.

Cheers.

Patrick
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

Expert Comment

by:dlmille
ID: 37864243
Well, that's just fabulous.

Well done!

But, may I ask what you mean to accomplish with this statement which will always be TRUE?

If Intersect(Range("E2"), Range("E14")) Is Nothing Then

Open in new window


and your "tooltip" has the same comment for the entire E2:E14 range, yes?


Dave
0
 

Author Comment

by:pg111
ID: 37864249
Hi Dave,

You're right about the Intersect statement. I was just wondering whether I could remove it without affecting the remaining code.

Actually each cell in the involved range (E2:E14) is being added a comment with its actual contents (value). Each tooltip is different.

So I could achieve what I wanted to get: a tooltip for each cell in the range.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37864263
Great - yes, you can remove the IF intersect is nothing then statement, and the end if that goes with it, or you could just change

IF INTERSECT() THEN to

If True Then 'and put comments here to remind you you could use an intersect statement her to gauge whether the code should run or not.

However, the only way to manage that is with the mouse hover routine, so unless you actually clicked in the cell and used the code on a selection change event, the intersect may have no value for you.

Cheers,

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now