Control Tip Text on Excel Command Buttons

Is there a way to have a control Tip Text on a Command Button on an excel
worksheet like the command buttons on a userform.

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




"Jason Zischke" wrote:

> Is there a way to have a control Tip Text on a Command Button on an excel
> worksheet like the command buttons on a userform.