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
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
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
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()