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.

3 thoughts 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.