I don't know how to put Tip Text into excel, but when I want to do this kind
of thing I use the shapes collection and just put a little baloon onto the
sheet and hide it after a few seconds. I wrote this a while ago so I don't
know how efficient it is, but it's always served me well. It's prehaps a
little more cartoonish then the tool tips but you could easily change the
shape if you want.
Public Sub ShowPopup(msg As String, displayTime As Integer)
'Just show the popup, size doesn't matter
Call ShowPopupDimensions(msg, displayTime, 95, 45)
End Sub
Public Sub ShowPopupDimensions(msg As String, displayTime As Integer, width
As Integer, height As Integer)
'Displays a yellow popup that informs the user of a drill through
'at the active cell. The displayTime param is the number of seconds
'that the pop remains visible.
Dim Descr As String, mydocument
Dim xwidth As Variant
Dim xpos As Variant
Dim ypos As Variant
'First destroy the shape named "pop" before you make it
Call hidePopup
Descr = msg
If Len(Descr) > 1 And IsEmpty(Descr) = False Then
Set mydocument = ActiveSheet
xwidth = ActiveCell.width
xpos = ActiveCell.Left
ypos = ActiveCell.Top
With mydocument.Shapes.AddShape(msoShapeRoundedRectangularCallout,
xpos + xwidth + 15, ypos - 40, width, height)
.Name = "pop"
End With
With ActiveSheet.Shapes("pop").Shadow
.ForeColor.RGB = RGB(128, 128, 128)
.OffsetX = 5
.OffsetY = -5
.Transparency = 0.5
.Visible = True
End With
With ActiveSheet.Shapes("pop").Fill
.ForeColor.RGB = RGB(255, 255, 220)
.BackColor.RGB = RGB(255, 255, 130)
.TwoColorGradient msoGradientHorizontal, 1
End With
ActiveSheet.Shapes("pop").Select
With Selection
.Characters.Text = Descr
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.AutoSize = False
.ShapeRange.Adjustments.Item(1) = -0.5
.ShapeRange.Adjustments.Item(2) = 1#
End With
ActiveCell.Select
End If
If displayTime < 10 Then
hideat = (Now + TimeValue("0:00:0" & displayTime))
Else
hideat = (Now + TimeValue("0:00:" & displayTime))
End If
Do While Now() < hideat
DoEvents
Loop
hidePopup
End Sub
Public Sub hidePopup()
'Loops through all the object in the ActiveSheet looking for the
'pop-up. If the popup is found it is deleted.
For Each s In ActiveSheet.Shapes
If s.Name = "pop" Then
'Debug.Print "Found pop!"
ActiveSheet.Shapes("pop").Delete
End If
Next
End Sub