Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / January 2008

Tip: Looking for answers? Try searching our database.

Tooltips with OptionButtons in worksheets

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
PO - 10 Jan 2008 17:46 GMT
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 - 10 Jan 2008 18:43 GMT
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

Signature

---
HTH

Bob

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

> Excel 2003, sp2
>
[quoted text clipped - 15 lines]
> Regards
> Pete
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.