Show Tag of a Commandbutton in an Excel sheet (Vba Macro)

[Deivid]

Hello, guys. Thank you for your work for so many years and that it has been very useful to me. There are a couple of things that escape me on my code. I am a user of office 2010 plus 64bits. I’d like that when I move the mouse over a CommandButton (FormBtn) it throws me a simple message (Label) descriptive of what the button does. The problem is that, the only thing I've found browsing the web, is a code, which I'm afraid isn't for 64bits:

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

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

'We declare a second function that allows us to create the label
Public Function Info(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

‘The screen is not updated only while the label is created and formatted
Application.ScreenUpdating = False

For Each objOLE In ActiveSheet.OLEObjects

‘There can only be one at a time.
If objOLE.Name = "TTL" Then objOLE.Delete

Next objOLE

[I]‘THE MACRO IS LAKED HERE. It tells me that the object can't be inserted[/I]
[B][I]Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")[/I][/B]

'We format it to make it look like an information window…
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

'We establish that the label disappears after 3 seconds of having removed the mouse...
Application.OnTime Now() + TimeValue("00:00:03"), "DeleteToolTipLabels"

End Function

'Finally, I create another procedure that allows us to delete the text from the label
Public Sub DeleteToolTipLabels()
Dim objToolTipLbl As OLEObject

For Each objToolTipLbl In ActiveSheet.OLEObjects

If objToolTipLbl.Name = "TTL" Then objToolTipLbl.Delete

Next objToolTipLbl

End Sub

‘Insert this into Sheet1
Sub FormBtn_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
Info FormBtn, "Formulario"
End If

End Sub

 

[John_w]

I am a user of office 2010 plus 64bits

To allow compilation of the code on Excel 64-bit, add the PtrSafe attribute to the API declarations:

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

 

[Deivid]

Same result.
An error ‘1004’ has occurred at runtime. Cannot insert the object

 

[John_w]

Is the sheet protected?

 

[Deivid]

Is the sheet protected?

Yes, the sheet is protected. but I can't unprotect it and protect it again and again every time the mouse hovers over the CommandButton. This doesn't seem very practical, does it?

 

[Deivid]

Is the sheet protected?

The problem is clear. The sheet would have to be unprotected and protected again to be able to insert and delete the label. I’m thinking other solution.

 

[RobOrBob]

The button is tied to a cell?

Simply add a note to the cell and when you hover over the button the note pops up (even when the cell is protected).

 

[John_w]

The sheet would have to be unprotected and protected again to be able to insert and delete the label.

Simply:

    ActiveSheet.Unprotect Password:="ThePassword"
    Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")
    objToolTipLbl.Name = "TTL"
    ActiveSheet.Protect Password:="ThePassword"

and delete the .Name = "TTL" inside the With ... End With block. Add the same Unprotect/Protect lines for deleting the label.

 

[Deivid]

The button is tied to a cell?

Simply add a note to the cell and when you hover over the button the note pops up (even when the cell is protected).

How to do that? I’ve watched checkboxes linked to a cell or textboxes but not CommandButtons

 

[Deivid]

Simply:

    ActiveSheet.Unprotect Password:="ThePassword"
    Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")
    objToolTipLbl.Name = "TTL"
    ActiveSheet.Protect Password:="ThePassword"

and delete the .Name = "TTL" inside the With ... End With block. Add the same Unprotect/Protect lines for deleting the label.

I will try later your option. Thanks guy