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:


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”
End If
‘Range(“A5”) = txtName.Text
If Not IsNumeric(Me.txtSalary.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Employee Data”
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()
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.

One thought on “Show User Form on Excel File Open”

  1. Option Explicit
    Private Sub ComboBox1_Change()
    Me.ComboBox2 = “”

    Select Case Me.ComboBox1
    Case “Department”
    Me.ComboBox2.RowSource = “Department”
    Case “Admin”
    Me.ComboBox2.RowSource = “Admin”

    Case “R_D”
    Me.ComboBox2.RowSource = “R_D”
    Case “Marketing”
    Me.ComboBox2.RowSource = “Marketing”
    Case “Sales”
    Me.ComboBox2.RowSource = “Sales”

    End Select
    End Sub

    Private Sub UserForm_Initialize()
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim i As Integer

    LastColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column

    With Sheets(“Sheet1”)
    For i = 1 To LastColumn
    With Columns(i)
    LastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    With Range(Cells(1, i), Cells(LastRow, i))
    Range(Cells(1, i), Cells(LastRow, i)).Select
    Selection.CreateNames Top:=True
    End With
    End With
    Next i
    End With
    Me.ComboBox1.RowSource = “Department”

    End Sub

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.