?
Solved

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

Posted on 2012-04-10
6
Medium Priority
?
2,989 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 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
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!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

777 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