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

 

[RobOrBob]

When you 'draw' a Button (Form Control) it by default has the property 'Move and size with cells'.

So if you draw it over several cells (wholly or partially) it will move and change size as any of those cells/rows are increased/decreased in size. So you can add a comment to those cells but if it covers several cells to which one do you add the comment? One of them or all of them?

But if the button is drawn within the boundaries of just one cell then the button is implicitly 'tied' to that cells size so that means the comment just needs to be on that cell for the comment to 'bleed through' when the use hovers over the button.

You can set a cell note in VBA too so you can make it dynamic for example from contents elsewhere.

Worksheets("Sheet1").Range("A1").NoteText "A comment for the form button"

 

[Joe4]

Deivid,
Please note that when marking a post as the solution, please mark the original post containing the solution, not your own post acknowledging another post is the solution.
I have updated this for you on this thread.