Open User Form Using Command Button

How to Open the UserForm using a Command Button on a Worksheet
You can make it easier for the user to open a user-form for data entry by inserting a command button on an Excel worksheet and attaching appropriate code to it. The attached code is shown below:

frmEmpData.Show

How to implement the process:

  • Click on the developer tab
  • In the ‘controls’ group select ‘Insert’
  • Under ‘ActiveX Controls’ click on a command button
  • Now click and drag on the Excel worksheet at an appropriate location a command button
  • Right-Click on the command button, select properties and assign an appropriate name and caption to it.
  • Double Click on the command button and enter the line of code shown above between the ‘sub…….’ and ‘end sub’ code lines as shown at the bottom of the complete code in bold.

Complete VBA code for the user-form:

Private Sub cmdAddData_Click()
Dim RowCount As Long
Dim benefits, total As Single
If Me.txtName.Value = “” Then
MsgBox “Please enter a name”, vbExclamation, “Employee Data”
Me.txtName.SetFocus
End If
‘Range(“A5”) = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Employee Data”
Me.txtSalary.SetFocus
End If
‘Range(“B5”) = txtSalary.Value
benefits = txtSalary.Value * 0.5
‘Range(“C5”) = benefits
total = txtSalary.Value + benefits
‘Range(“D5”) = total
RowCount = Worksheets(“Sheet1”).Range(“A4”).CurrentRegion.Rows.Count
With Worksheets(“Sheet1”).Range(“A4”)
.Offset(RowCount, 0) = Me.txtName.Value
.Offset(RowCount, 1) = Me.ComboBox1.Value
.Offset(RowCount, 2) = Me.txtSalary.Value
.Offset(RowCount, 3) = benefits
.Offset(RowCount, 4) = total
End With
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Then
ctl.Value = “”
End If
Next ctl
End Sub

Private Sub Workbook_Open()
frmEmpData.Show
End Sub

Private Sub cmdOpenForm_Click()
frmEmpData.Show
End Sub

With this Excel VBA (macros) training video we have completed the process of creating a user-form for data entry. Of course, you can now create your own forms according to your needs.

2 thoughts on “Open User Form Using Command Button

  1. Kartthik

    Dear Dinesh,

    Is it possible to assign a unique worksheet on every single item of the listbox, so that when a specific item is selected in the listbox, the data should be captured in the corresponding unique sheet.

    For example: The items in the Listbox are Names, Department and Block. On the workbook there are 3 worksheets named Names, Department and Block. So if a user selects Names from the Listbox the the data should be captured in the Names worksheet and so on.

    Reply
  2. Pingback: Best Training on Advanced Excel and VBA in Bangalore | jaseemblog

Leave a Reply

Your email address will not be published. Required fields are marked *