Tooltips with OptionButtons in worksheets

[PO]

Excel 2003, sp2

Hi,

I have inserted several optionbuttons, grouped together in groupboxes, in a
worksheet.
The optionbuttons cover one cell each (so that the user may click anywhere
inside the cell to check the optionbutton. Everything works as intended but
I would also like tooltips to be displayed when the mousepointer is moved
over the optionbutton/cell. There doesn't seem to be such a function with
controls used directly in worksheets (as opposed to optionbuttons used in
forms where you can set the ControlTipText property).

I tried to use hyperlinks and comments as a workaround but their "tooltip"
doesn't show since the optionbuttons cover the entire cell thus blocking the
mouseover event. Is there any other workaround that works?

Regards
Pete

 

[Bob Phillips]

To do this, put the following code in a standard code module (making it
available to the whole workbook).

'------------- bas module ------------------------
Option Explicit

Declare Function GetSystemMetrics Lib "user32" ( _
ByVal nIndex As Long) As Long

Declare Function GetSysColor Lib "user32" ( _
ByVal nIndex As Long) As Long


Public Function CreateToolTipLabel(objHostOLE As Object, _
sTTLText As String) As Boolean
Dim objToolTipLbl As OLEObject
Dim objOLE As OLEObject

Const SM_CXSCREEN = 0
Const COLOR_INFOTEXT = 23
Const COLOR_INFOBK = 24
Const COLOR_WINDOWFRAME = 6

Application.ScreenUpdating = False 'just while label is created and
formatted

For Each objOLE In ActiveSheet.OLEObjects
If objOLE.Name = "TTL" Then objOLE.Delete 'only one can exist at a
time
Next objOLE

'create a label control...
Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")

'...and format it to look as a ToolTipWindow
With objToolTipLbl
.Top = objHostOLE.Top + objHostOLE.Height - 10
.Left = objHostOLE.Left + objHostOLE.Width - 10
.Object.Caption = sTTLText
.Object.Font.Size = 8
.Object.BackColor = GetSysColor(COLOR_INFOBK)
.Object.BackStyle = 1
.Object.BorderColor = GetSysColor(COLOR_WINDOWFRAME)
.Object.BorderStyle = 1
.Object.ForeColor = GetSysColor(COLOR_INFOTEXT)
.Object.TextAlign = 1
.Object.AutoSize = False
.Width = GetSystemMetrics(SM_CXSCREEN)
.Object.AutoSize = True
.Width = .Width + 2
.Height = .Height + 2
.Name = "TTL"
End With
DoEvents
Application.ScreenUpdating = True

'delete the tooltip window after 5 secs
Application.OnTime Now() + TimeValue("00:00:05"), "DeleteToolTipLabels"

End Function

Public Sub DeleteToolTipLabels()
Dim objToolTipLbl As OLEObject
For Each objToolTipLbl In ActiveSheet.OLEObjects
If objToolTipLbl.Name = "TTL" Then objToolTipLbl.Delete
Next objToolTipLbl
End Sub


'------------end of bas module -------------

Then in the code module for the sheet that has the control, add some
mousedown event code. To get to this module, right-click on the sheet name
tab, and selecw code (or double-click on the sheet name from within the VB
IDE). Here is an example of how to call it, assuming that the optionbuttons
are called OptionButton1 and Optionbutton2

Private Sub OptionButton1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
Dim objTTL As OLEObject
Dim fTTL As Boolean

For Each objTTL In ActiveSheet.OLEObjects
fTTL = objTTL.Name = "TTL"
Next objTTL

If Not fTTL Then
CreateToolTipLabel OptionButton1, "Optionbutton1 ToolTip"
End If

End Sub


Private Sub OptionButton2_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
Dim objTTL As OLEObject
Dim fTTL As Boolean

For Each objTTL In ActiveSheet.OLEObjects
fTTL = objTTL.Name = "TTL"
Next objTTL

If Not fTTL Then
CreateToolTipLabel OptionButton2, "Optionbutton2 ToolTip"
End If

End Sub




--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)