learn microsoft excel

How to Open the UserForm Automatically and Use it Immediately

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

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





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