Solved

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

Posted on 2012-04-10
6
2,381 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

773 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