Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding tool tip to worksheet button
I have a button in a worksheet cell that runs a VBA sub (fortunately this
works fine!) Is there any way to add a tool tip to the button? Thanks Sue |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding tool tip to worksheet button
Hi
if it's a button from the toolbox toolbar set the ControlTip property of this button -- Regards Frank Kabel Frankfurt, Germany "SueJB" schrieb im Newsbeitrag ... I have a button in a worksheet cell that runs a VBA sub (fortunately this works fine!) Is there any way to add a tool tip to the button? Thanks Sue |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding tool tip to worksheet button
Setting the ControlTipText works ok in a userform. But I couldn't get it to
work on a commandbutton on a worksheet. Frank Kabel wrote: Hi if it's a button from the toolbox toolbar set the ControlTip property of this button -- Regards Frank Kabel Frankfurt, Germany "SueJB" schrieb im Newsbeitrag ... I have a button in a worksheet cell that runs a VBA sub (fortunately this works fine!) Is there any way to add a tool tip to the button? Thanks Sue -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding tool tip to worksheet button
It's a control button added from the Control Toolbox toolbar, but if I
right-click for the properties listing it doesn't show an entry for ControlTipText. I'm puzzled! Sue "Dave Peterson" wrote: Setting the ControlTipText works ok in a userform. But I couldn't get it to work on a commandbutton on a worksheet. Frank Kabel wrote: Hi if it's a button from the toolbox toolbar set the ControlTip property of this button -- Regards Frank Kabel Frankfurt, Germany "SueJB" schrieb im Newsbeitrag ... I have a button in a worksheet cell that runs a VBA sub (fortunately this works fine!) Is there any way to add a tool tip to the button? Thanks Sue -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding tool tip to worksheet button
That is true, it is only on forms, userforms, that has that property.
It can be emulated with control toolbox buttons using a few frigs. Here is an example for a button named CmdTooltipTest 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 3 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 select 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 command button is called cmdTooltipTest Private Sub CmdTooltipTest_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 cmdTooltipTest, "ToolTip Label" End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "SueJB" wrote in message ... It's a control button added from the Control Toolbox toolbar, but if I right-click for the properties listing it doesn't show an entry for ControlTipText. I'm puzzled! Sue "Dave Peterson" wrote: Setting the ControlTipText works ok in a userform. But I couldn't get it to work on a commandbutton on a worksheet. Frank Kabel wrote: Hi if it's a button from the toolbox toolbar set the ControlTip property of this button -- Regards Frank Kabel Frankfurt, Germany "SueJB" schrieb im Newsbeitrag ... I have a button in a worksheet cell that runs a VBA sub (fortunately this works fine!) Is there any way to add a tool tip to the button? Thanks Sue -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding tool tip to worksheet button
Bob -
Wow! Many thanks for this - I am only just starting VBA programming so your suggestion may be a little too much for me right now, but I shall come back to it later. Sue "Bob Phillips" wrote: That is true, it is only on forms, userforms, that has that property. It can be emulated with control toolbox buttons using a few frigs. Here is an example for a button named CmdTooltipTest 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 3 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 select 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 command button is called cmdTooltipTest Private Sub CmdTooltipTest_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 cmdTooltipTest, "ToolTip Label" End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "SueJB" wrote in message ... It's a control button added from the Control Toolbox toolbar, but if I right-click for the properties listing it doesn't show an entry for ControlTipText. I'm puzzled! Sue "Dave Peterson" wrote: Setting the ControlTipText works ok in a userform. But I couldn't get it to work on a commandbutton on a worksheet. Frank Kabel wrote: Hi if it's a button from the toolbox toolbar set the ControlTip property of this button -- Regards Frank Kabel Frankfurt, Germany "SueJB" schrieb im Newsbeitrag ... I have a button in a worksheet cell that runs a VBA sub (fortunately this works fine!) Is there any way to add a tool tip to the button? Thanks Sue -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding tool tip to worksheet button
Sue,
It's pretty straight-forward if you follow my guiding notes. Just change the text to what you require. -- HTH RP (remove nothere from the email address if mailing direct) "SueJB" wrote in message ... Bob - Wow! Many thanks for this - I am only just starting VBA programming so your suggestion may be a little too much for me right now, but I shall come back to it later. Sue "Bob Phillips" wrote: That is true, it is only on forms, userforms, that has that property. It can be emulated with control toolbox buttons using a few frigs. Here is an example for a button named CmdTooltipTest 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 3 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 select 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 command button is called cmdTooltipTest Private Sub CmdTooltipTest_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 cmdTooltipTest, "ToolTip Label" End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "SueJB" wrote in message ... It's a control button added from the Control Toolbox toolbar, but if I right-click for the properties listing it doesn't show an entry for ControlTipText. I'm puzzled! Sue "Dave Peterson" wrote: Setting the ControlTipText works ok in a userform. But I couldn't get it to work on a commandbutton on a worksheet. Frank Kabel wrote: Hi if it's a button from the toolbox toolbar set the ControlTip property of this button -- Regards Frank Kabel Frankfurt, Germany "SueJB" schrieb im Newsbeitrag ... I have a button in a worksheet cell that runs a VBA sub (fortunately this works fine!) Is there any way to add a tool tip to the button? Thanks Sue -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding button to worksheet to refresh pivot table data | Excel Worksheet Functions | |||
tool bar button | New Users to Excel | |||
Adding Macro to Button on WOrksheet | Excel Discussion (Misc queries) | |||
adding a button to a tool bar | Excel Worksheet Functions | |||
Adding Button to Tool Bar | Excel Discussion (Misc queries) |