613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

VBA Chapter 24A of 24: The Command Buttons  in VBA for Excel

In the toolbox the command button has this icon   . The command button  is a very active  control and there is always VBA code behind it.

The command buttons are usually placed at the bottom of the form and serve to complete the transaction for which the form has been created. The caption of these buttons are usually "Go" , "Run" , "Submit" , "Cancel" , etc.

Properties

The other interesting properties of the command button  are:

- WordWrap to be able to write more that one line on a button,
- ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button,

For advanced users there are the:
- Enabled and Visible properties that you can change programmatically to disable or render invisible a command button following a previous selection in another control of the userform.

Code

Name your command button before developing your code. VBA uses the name of the command button when it creates lines of code related to events. So if you don't name your command button VBA will create the private sub::
Private Sub CommandButton1_Click()
as if you name the command Button "cmbSubmit" for example the private sub will start with:
Private Sub cmbSubmit_Click()

A very simple VBA procedure for the command button would look like this:

Private Sub cmbSubmit_Click()
                  Sheets("Code").Range("F1").Value = cbxInput.Value
                  frmPassword.Hide
End Sub

The content of the combo box "cbxInput" is entered in cell "F1" of the sheet "Code" and the form (frmPassport) is closed.

613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links