View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Learn-more Learn-more is offline
external usenet poster
 
Posts: 25
Default Add comment to command button

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