learn microsoft excel

How to Open the UserForm using a Command Button on a Worksheet

Excel 2007
Excel 2003
Macros Excel 2007
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training

Books


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.




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.
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Books