Solved

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

Posted on 2012-04-10
6
2,589 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 42

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

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 42

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 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