Adding code to controls in Excel – Step 3 of 3
Control and Dialog Box Events in Excel
After you have added controls to your dialog box or document, you add event procedures to determine how the controls respond to user actions.
User forms and controls have a predefined set of events. For example, a command button has a Click event that occurs when the user clicks the command button, and UserForms have an Initialize event that runs when the form is loaded.
To write a control or form event procedure, open a module by double-clicking the form or control, and select the event from the Procedure drop-down list box.
Event procedures include the name of the control. For example, the name of the Click event procedure for a command button named Command1 is Command1_Click.
If you add code to an event procedure and then change the name of the control, your code remains in procedures with the previous name.
For example, assume you add code to the Click event for Commmand1 and then rename the control to Command2. When you double-click Command2, you will not see any code in the Click event procedure. You will need to move code from Command1_Click to Command2_Click.
To simplify development, it’s a good practice to name your controls in Excel before writing code.
We have added three labels, 2 textboxes, one combobox and one command button and explained the complete procedure of transferring the data from the userform onto the Excel worksheet in the training video below.
On clicking the command button ‘Clear’ data on the form is cleared. The ‘End’ button is clicked when you are finished with your data entries. Both these buttons have not been shown here but you can add them on your userform for practice and use the given code.
Private Sub cmdTransfer_Click()
‘Range(“A2”).Value = txtItem.Text
‘Range(“B2”).Value = Val(txtPrice.Text)
‘Range(“C2”).Value = cmbQty.Value
Dim eRow as Long
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(eRow, 1).Value = txtItem.Text
Cells(eRow, 2).Value = Val(txtPrice.Text)
Cells(eRow, 3).Value = cmbQty.Value
Cells(eRow, 4).Value = Cells(eRow, 2).Value * Cells(eRow, 3).Value
Private Sub cmdClear_Click()
txtItem.Text = “”
txtPrice.Text = “”
Private Sub cmdEnd_Click()