pg111
asked on
Create a tooltip displaying the contents of a cell in VBA for Excel 2003
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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<>"";Pro j_Trad1_no m;"").
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:
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
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<>"";Pro
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
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
Well, that's just fabulous.
Well done!
But, may I ask what you mean to accomplish with this statement which will always be TRUE?
and your "tooltip" has the same comment for the entire E2:E14 range, yes?
Dave
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
and your "tooltip" has the same comment for the entire E2:E14 range, yes?
Dave
ASKER
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.
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.
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
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
ASKER
I will think it over and make sure to let you know of the outcome.
Cheers.
PG