Solved

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

Posted on 2012-04-10
6
2,200 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 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

11 Experts available now in Live!

Get 1:1 Help Now