Show User Form on Excel File Open

How to Open the UserForm Automatically on Opening an Excel File or Workbook
For the end-user it is a cumbersome process to start the Visual Basic Editor, click run and then use the user-form. We need to give her access to the form as soon as she opens the file. There are many manual ways of doing this like creating a macro and then linking the macro module to a toolbar item or a command button on the Excel worksheet. The other alternative method is to open the user-form automatically when the user opens the concerned file so that work starts immediately. To do this you need only a single line of code either in the macro module or in ‘this workbook’ as shown in the training video. The line of code is mentioned here:

frmEmpData.Show


Here is the 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

In the next video we’ll learn how to create a command button on the worksheet and link it to the macro that will open the form.

Leave a Reply

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