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

Adding a comment/instruction to a command button in Excel using VBA programming

1. Right-click on the command button and select "View Code". This will open the VBA editor.
2. In the VBA editor, find the code for the command button. It should look something like this:
   
   
   Formula:

   Private Sub CommandButton1_Click()
    'Insert your code here
End Sub 

3. To add a comment/instruction, simply type a single quote (') before the text you want to add. For example:
   
   
   Formula:

   Private Sub CommandButton1_Click()
    'This is a comment that will appear when the cursor is placed on the button
    'Insert your code here
End Sub 

4. Save your changes and close the VBA editor.
   

Now, when you hover your cursor over the command button, the comment/instruction you added will appear as a tooltip. Note that the comment will not appear when the button is clicked, as it is only meant to provide additional information to the user.

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

Rick


"Learn-more" wrote in message
...
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

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

I bet you missed that first sentence.

"Rick Rothstein (MVP - VB)" wrote:

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

Rick

"Learn-more" wrote in message
...
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

--

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.

Learn-more wrote:

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

--

Dave Peterson

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)" wrote in
message ...
If you can fit your comment on one line, and if it isn't too long, you can
use the ControlTipText property.

Rick


"Learn-more" wrote in message
...
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

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


"Dave Peterson" wrote in message
...
I don't think that this will work for commandbuttons placed on the
worksheet.

I bet you missed that first sentence.

"Rick Rothstein (MVP - VB)" wrote:

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

Rick

"Learn-more" wrote in message
...
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

--

Dave Peterson

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)" wrote:

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)" wrote in
message ...
If you can fit your comment on one line, and if it isn't too long, you can
use the ControlTipText property.

Rick


"Learn-more" wrote in message
...
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

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" wrote in message
...
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)" wrote:

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)" wrote
in
message ...
If you can fit your comment on one line, and if it isn't too long, you
can
use the ControlTipText property.

Rick


"Learn-more" wrote in message
...
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

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)" wrote:

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" wrote in message
...
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)" wrote:

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)" wrote
in
message ...
If you can fit your comment on one line, and if it isn't too long, you
can
use the ControlTipText property.

Rick


"Learn-more" wrote in message
...
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

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