Add comment to command button

[Learn-more]

In my worksheet, I had a few command buttons set up which when hit will call
a VBA subroutine/macro etc.

Anyway I can add comment to it so when the curser was placed on the command
button, then a comment/instruction will show up. I can only position on top
but not hitting the button as it will run a macro when hit.

Anything like that in VBA programming?

Thanks for any help.
Learn-more

 

[Rick Rothstein \(MVP - VB\)]

If you can fit your comment on one line, and if it isn't too long, you can
use the ControlTipText property.

Rick

 

[Dave Peterson]

I don't think that this will work for commandbuttons placed on the worksheet.

I bet you missed that first sentence.

 

[Dave Peterson]

Maybe you could plop the commandbutton in an oversized cell and add a comment to
that cell.

The user will see the comment until the mouse is over the commandbutton itself.

 

[Rick Rothstein \(MVP - VB\)]

Dave was right... I missed that you said the CommandButton was on your
worksheet. Here is a kludge method which will mimic what you want to do.

First off, ALL controls (including the CommandButton, which I'll assume is
named CommandButton1 for this example) must be from the Control Toolbox
toolbar... NOT the Forms toolbar. It is important that you do the next two
steps in the order I show them. The first step is to display the Control
Toolbox which will also place you in Design Mode.

Now, place a Label control (which I'll assume is named Label1 for this
example) on the worksheet so it surrounds CommandButton1 completely (that
is, make it so CommandButton1 is roughly centered within Label1)... and make
Label1 VERY large (the larger the better). Right click Label1 and select
Order/SendToBack from the popup menu that appears.

Next, place another Label control on the worksheet (which I'll assume is
named Label2 for this example). Label2 is going to display your popup
comment when the mouse moves across the CommandButton. Right click Label2
and select Order/SendToBack from the popup menu that appears.

Right click any of the controls and select Properties from the popup menu
that appears... this will display the Properties Window. You can either
click on a control directly, or select the control's name from the drop down
box on the Properties Window to bring up the property settings for any
particular control. Set the following properties for each Label as shown (I
am assuming you have already set up any required properties and code for the
CommandButton when you first placed it on your worksheet).

Label1
=========
BackStyle = 0 - fmBackStyleTransparent
BorderStyle = 0 - fmBorderStyleNone
Caption = <<There will be NO caption; delete any text>>
Visible = False

Label2
=========
AutoSize = False
BackColor = &H00C0FFFF& (this is a pale yellow color)
BackStyle = 1 - fmBackStyleOpaque
BorderColor = &H00000000& (this is a pure black color)
BorderStyle = 1 - fmBorderStyleSingle
Caption = "Place your single line comment here" <<See Note>>
ForeColor = &H00000080& (this is a very dark red color)
Height = <<See Note>>
Visible = False
Width = <<See Note>>

NOTE
=========
You can make Label2's Caption (your comment) multiple lines if you want, but
you cannot do that from the Property Window... you will have to do that in
code. I'll show you a place you can do this in the code below; I am just
mentioning it here because I wanted to note that the settingss for Label2's
Height and Width properties are dependent on the text you use for your
comment.

Now, right click any control and select View Code from the popup menu that
appears. Copy/Paste this code in the code window that appeared when you did
that...

' *************** START OF CODE ***************
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
'
' Uncomment the next two lines if you want
' to display a multiple line comment
'
'Const Comment As String = "This is how you would display " & _
' vbLf & "a multiple line comment"
'If Label2.Caption <> Comment Then Label2.Caption = Comment
Label1.Visible = True
Label2.Visible = True
End Sub

Private Sub Label1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
Label1.Visible = False
Label2.Visible = False
End Sub
' *************** END OF CODE ***************

Okay, one final step... go back to the worksheet and close the Properties
Window and the Control Toolbox toolbar and turn Design Mode off. That's
it... you are done. when you slide your mouse over the CommandButton, your
comment should appear and when you move the mouse off of the CommandButton,
the comment should disappear. You should also be able to select any cells
around the CommandButton normally.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Yep, I did miss that the OP said "in my worksheet". So you are right, my
original post will not work; however, I just posted a kludge method that
will offer roughly the same functionality (with the side benefit that a
multiple line comment is supported).

Rick

 

[Learn-more]

Hello Everyone,

Thank you all for the help. I will digest and try it out as I am below you
guys level on VBA. I will add it to my program and see how it works.

Thanks again.

Learn-more

 

[Rick Rothstein \(MVP - VB\)]

Just follow the instructions I provided step-by-step and you should be fine.
Just to reiterate... don't be afraid to make Label1 large, the larger the
better. If you run into any problems, just write back here explaining what
you did and what happened afterwards.

Rick

 

[Learn-more]

Thanks Rick,

It works to show the comment I needed. However, I still need to explore a
bit more as in some command as I move to it, it will also run the command
before I need to hit it.

I think I need to learn more on the VBA command like "ByVal", "MouseMove"
etc to get full use of VBA. You people are great.

One question, is the Label1 area is to hide the comment? As when I do the
test, I put Label1 away from the command button and as I move away from the
command button and close to the Label1, then the comment hidden again.

Thanks so much. I really learn something and also the tricks on programming.

Learn-more

 

[Rick Rothstein \(MVP - VB\)]

One question, is the Label1 area is to hide the comment? As when I do the

test, I put Label1 away from the command button and as I move away from
the
command button and close to the Label1, then the comment hidden again.

No, that is not what Label1 is for. No event is triggered when the mouse
leaves a control, so I need to come up with a way to know when the mouse was
no longer over the CommandButton so I could hide the comment label. The
MouseMove property of a control triggers while the mouse is moving on that
control, so I added (the invisible) Label1 in order to use its MouseMove
event to detect when the mouse was no longer over the CommandButton (the
mouse will only be over one or the other control, but not both at the same
time) so I could decide when to hide the comment (Label2). The reason I said
to make Label1 large is because, if it is too small, it is possible for the
user to move the mouse fast enough that the initial MouseMove event for
Label1 would not register until after the mouse moved off of it... if that
happens, the comment would not be hidden. The larger Label1 is, the less
chance a user will be able to move the mouse faster than the MouseMove event
can react to it.

Thanks so much. I really learn something and also the tricks on
programming.

My pleasure.

Rick