In , Dallman Ross <dman@localhost.
spake thusly:

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



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. (!!)

--
dman