A CommandButton control is a clickable button usually associated with an action such as closing the form, opening another form, saving/submitting the data in the form, etc. Therefore, it always (or almost always) comes along a Click event procedure. We can also display text and pictures on a CommandButton. The toolbox symbol for a CommandButton control is highlighted in the image below.
CommandButton Properties
As with other
controls, the Name property represents the object while the Caption is the text
on the button. The Userform example below has three command buttons. The
selected button has been given the caption “CLOSE” at design-time. The name of
that button is the default (CommandButton3).
As we will see in the
next chapter, each control, or most of them, should be given a meaningful name
that we can easily recognize and associate with the purpose of that control in
order to keep the VBA project and code organized. The close button above could be
given the name cmbClose or btnClose, for example.
We can change the
background color, font color, size, or style, to make the button stand out or
more appealing. The picture below shows a standard command button with default
format (Button1), another button with a different background color (Button2),
and another with other back and fore color, and font size and style (Button3).
We can also add a
picture to the button using the properties Picture and PicturePosition. The
default mouse pointer is the north-west arrow. We can change the cursor style
with the property MousePointer and choose from various options. The last option
is “Custom”, which requires a customized cursor image added to MouseIcon in the
properties window.
CommandButton Events
The main and default
event of the CommandButton control is the Click event. That’s all we need to
associate the button with an action. The action could be closing the form,
opening another form, or calling a macro to do something (e.g. saving or
writing the data to the worksheet). The code below closes the Userform when
clicking the button (CommandButton3, which in the example above was the close
button). As explained earlier, the name of this button could be changed for
example to cmbClose.
Unload Me
End Sub
This other example
saves the value added to a text box and then calls another macro to process
that data when clicking the button (CommandButton1).
userInput = TextBox1.Value
Call otherMacro(userInput)
End Sub
See below the full
list of events for a CommandButton control. There are not so many though, less
than for a TextBox or ComboBox. In fact, there is not much to do with the
button other than recognizing when is clicked. However, we may want to capture a double click
(DblClick event) or use some of the mouse-related events in some scenarios.
We may want to run
some checks before closing the Userform, or opening another form, or calling
some macro, upon a button click. We can have the code below added to the Click
event procedure to do that. It could also go in a MouseDown procedure, and,
regardless of having another Click event to close the form, the fact of
displaying a message box on mouse down, would prevent from triggering the Click
event. The MouseDown and MouseUp events
occur before the Click event.
Dim confirm As VbMsgBoxResult
confirm = MsgBox("You did not save the data. Do you want to exit?", vbYesNo)
If confirm = vbYes Then Unload Me
End Sub
We can also use
MouseDown and MouseUp to format the button and create an effect that confirms
the button was clicked, for example. The two event procedures below work
together to change the background color of the button (to blue) when clicked.
The MouseUp returns the original color of the button (&H8000000F).
CommandButton1.BackColor = vbBlue
End Sub
CommandButton1.BackColor = &H8000000F
End Sub
No comments:
Post a Comment