VBA to add Tool Tips

[Dallman Ross]

In Excel 2002 I have a column in which I would like to add
Tool Tips -- each cell's Tip being the value of the formula
in that cell. (Or I could go collect the value from another
sheet in the workbook if need be.) I'm trying some VBA
to do this, but so far without success. Can someone help?

 

[D.]

In Excel 2002 I have a column in which I would like to add
Tool Tips -- each cell's Tip being the value of the formula
in that cell. (Or I could go collect the value from another
sheet in the workbook if need be.) I'm trying some VBA
to do this, but so far without success. Can someone help?

try this,
run the code in sheet1
Have something entered in sheet2 E1

Sub Macro3()
' Macro3 Macro
' Macro recorded 8/4/2007 by Dave Morrison
Range("A1").ClearComments
With Range("A1")
.AddComment Text:=Sheets("Sheet2").Range("E1").Text
ActiveCell.Comment.Visible = False
End With
End Sub

 

[Dallman Ross]

Dave,

Your simple macro which you recorded gave me a needed nudge.
I had also recorded some macros whilst scratching my head.
But I thought about this some more and tried and tried things
until I got it. Thanks.

Comments are sort of okay, but I really wanted tool tips. I
succeeded. I will post my code. This works fine, though
comments or suggested refinements always gladly accepted.
I couldn't get one part to work until I tried adding ".Activate"
out of exasperation, and suddenly it worked. Wow.

I have one more question, which I'll post below the code.

----------------------
Sub tipMe()
' 8/4/2007 by Dallman Ross, with a nudge by Dave Morrison

Dim indie As Variant
Dim myRow As Long

Dim datRows As Long


ThisWorkbook.Worksheets("Stagger").Activate

datRows = Application.CountA(Columns("D"))

For myRow = 2 To datRows + 1

' values in Column-A cells are also datasheet names
indie = Cells(myRow, "A").Value

With Cells(myRow, "D")

' below works okay, but I commented it out vice stuff _
further down below

' .ClearComments
' .AddComment Text:=Sheets(indie).Range("E2").Text
' .Comment.Visible = False

' this is what I was after! -dman
.Activate
.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=indie & "!E2", _
ScreenTip:=Sheets(indie).Range("E2").Text
End With

Next 'myRow
End Sub
----------------------

Okay, my followup question is, I'd prefer to have the screen tip
say the value of this instead of what I have there above:

=MSNStockQuote($A2,"Close")

(but using the "myRow" instead of the 2 there). I can't
figure out how to do it.

The reason I prefer that is, the data on the other referenced
worksheets may or may not be updated to yesterday's close.
Also, on the weekend the MSNStockQuote function still gives me
Thursday's close, but my updated data on the sheets I'm referencing
give me Friday's. I want the *prior* close. (During the week
what I have will be fine, so long as my sheets are updated through
the previous day.)

Oh, and the reason I want a tool tip instead of just looking in
the cell is, the cell is formatted to 0.5 points' width and is
merely fill-colored to show a trend. I've simply stuck the data
there as a safe "storage" place, but now want the tool tip to show
the text from the formula that's there.

 

[Dallman Ross]

I've managed to solve my problem here. It took me all night,
but I did it. Learned a few things.

One goofy thing I learned is, the tool tip won't display right if
the cell that was used to copy its contents from is not wide enough!
That took me a while to understand what was happening there.

I've gone with a temporary "scratch" range to copy the values into
that I want to turn into tool tips. It works well. I left some
of my earlier stuff in the code to do with the other sheets and
hyperlinks to them, because it is still useful to me.

Another thing I learned (not the first time, unfortunately) is not
to type "EntireRow.Delete" when I meant to type "EntireColumn.Delete";
and especially not before saving my current work first before running
the macro.

A third thing I learned -- is this a bug in Excel 2002? -- is
that running the code .Hyperlinks.Delete causes underlying cell
formatting to be lost. (!!)

Thanks to Dave Morrison for a small kick in the pants to get me
going on this. Now I can move on to the next thing I want to do.
(Spinner coding -- I have no idea yet, and my question over in
the programming group is so far unanswered.)

Here's the tool-tip code. It might be useful in parts to others.

------------------------
Sub tipMe()
'
' 8/5/2007 by Dallman Ross, with a nudge by Dave Morrison
'
' Keyboard Shortcut: Ctrl+Shift+T

Dim myRow, scratchCol, datRows As Long
Dim rgCopy, rgScratch As Range
Dim indie As Variant


With Application
.ScreenUpdating = False
.EnableEvents = False
End With

ThisWorkbook.Worksheets("Stagger").Activate

datRows = Application.CountA(Columns("D"))

scratchCol = 19 'Column "S" -- must be empty!!
Set rgCopy = Range("D2", Cells(datRows + 1, "D"))
Set rgScratch = Range(Cells(2, scratchCol), _
Cells(datRows + 1, scratchCol))

rgCopy.Copy
With rgScratch
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.NumberFormat = "#,##0.00_);(#,##0.00)"
End With


For myRow = 2 To datRows + 1
' value in cell is also a sheet name
indie = Cells(myRow, "A").Value

With Cells(myRow, "D")
.Activate
.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=indie & "!E2", _
ScreenTip:=Cells(myRow, scratchCol).Text
'ScreenTip:=Sheets(indie).Range("E2").Text 'alternate

End With
Next 'myRow

rgScratch.EntireColumn.Delete

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub


-dman

==================================================

I had written earlier:

Okay, my followup question is, I'd prefer to have the screen tip
say the value of [the following] instead of what I have there above:

=MSNStockQuote($A2,"Close")

(but using the "myRow" instead of the 2 there). I can't
figure out how to do it.

The reason I prefer that is, the data on the other referenced
worksheets may or may not be updated to yesterday's close.
Also, on the weekend the MSNStockQuote function still gives
me Thursday's close, but my updated data on the sheets I'm
referencing give me Friday's. I want the *prior* close. (During
the week what I have will be fine, so long as my sheets are
updated through the previous day.)

Oh, and the reason I want a tool tip instead of just looking in
the cell is, the cell is formatted to 0.5 points' width and is
merely fill-colored to show a trend. I've simply stuck the data
there as a safe "storage" place, but now want the tool tip to
show the text from the formula that's there.

 

[Dallman Ross]

I've managed to solve my problem here. It took me all night, but
I did it. Learned a few things.

One goofy thing I learned is, the tool tip won't display right
if the cell that was used to copy its contents from is not wide
enough! That took me a while to understand what was happening
there.

I've gone with a temporary "scratch" range to copy the values

One more follow-up to myself, because I've reworked it again and
simplified. The code might be helpful to others. The "goofy"
bug I elucidated above is the key. It was what drove me to thinking
my original approach was no good. But it turns out I don't need a
helper column at all, or any copying. I just widen the column,
add the screen tip, and restore the column's width to the original
(0.5 points). Here we go:

Sub tipMe()
'
' 8/5/2007 by Dallman Ross
' Add screen tips with hyperlinks to a range
'
' Keyboard Shortcut: Ctrl+Shift+T

Dim myRow, datRows As Long
Dim myColWidth As Variant
Dim rgIndirect As Variant

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

ThisWorkbook.Worksheets("Stagger").Activate
datRows = Application.CountA(Columns("D"))

With Columns("D")
myColWidth = .ColumnWidth 'save current width
Columns("D").AutoFit 'or screentip won't work
End With

For myRow = 2 To datRows + 1

' value in cell is also a sheet name
rgIndirect = Cells(myRow, "A").Value & "!E2"

With Cells(myRow, "D")
.Activate
.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=rgIndirect, _
ScreenTip:=.Text 'from this cell
End With
Next 'myRow

Columns("D").ColumnWidth = myColWidth 'restore original
Range("A1").Select

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

 

[D.]

One more follow-up to myself, because I've reworked it again and
simplified. The code might be helpful to others. The "goofy"
bug I elucidated above is the key. It was what drove me to thinking
my original approach was no good. But it turns out I don't need a
helper column at all, or any copying. I just widen the column,
add the screen tip, and restore the column's width to the original
(0.5 points). Here we go:

Sub tipMe()
'
' 8/5/2007 by Dallman Ross
' Add screen tips with hyperlinks to a range
'
' Keyboard Shortcut: Ctrl+Shift+T

Dim myRow, datRows As Long
Dim myColWidth As Variant
Dim rgIndirect As Variant

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

ThisWorkbook.Worksheets("Stagger").Activate
datRows = Application.CountA(Columns("D"))

With Columns("D")
myColWidth = .ColumnWidth 'save current width
Columns("D").AutoFit 'or screentip won't work
End With

For myRow = 2 To datRows + 1

' value in cell is also a sheet name
rgIndirect = Cells(myRow, "A").Value & "!E2"

With Cells(myRow, "D")
.Activate
.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=rgIndirect, _
ScreenTip:=.Text 'from this cell
End With
Next 'myRow

Columns("D").ColumnWidth = myColWidth 'restore original
Range("A1").Select

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

congrads,
one thing I forgot was that you can use tool tips from data validation

 

[Dallman Ross]

congrads, one thing I forgot was that you can use tool tips from
data validation

Cool. I never knew that! Good to know.