Excel VBA: How to Create a User Form

To Create a User Form in Excel. Press Alt + F11 on your Keyboard.
You will see the below image.

Now go to Insert => User Form

Rename the Form by going to properties
Name as SimpleForm (This name will be used to call from VBA Code)
Caption as Simple Form (This name will be displayed on the Form)

Now Add Three Labels , Text Box and Command Button.

Double Click on Submit Button and add below code.

Private Sub btnSubmit_Click()

'Declare the Variables
Dim DATA As Worksheet
Dim LastRow As Integer

'Assinging DATA as worksheet Object
Set DATA = Worksheets("DATA")

'Finding LastRow in DATA Sheet
LastRow = DATA.Cells.SpecialCells(xlCellTypeLastCell).Row

'Validating all the fields to check all the fields are entered

If txtName.Value = "" Then
MsgBox "Please Enter a Valid Name", , "Simple Form"
Exit Sub
ElseIf txtCity.Value = "" Then
MsgBox "Please Enter a Valid City Name", , "Simple Form"
Exit Sub
ElseIf txtCountry.Value = "" Then
MsgBox "Please Enter a Valid Country Name", , "Simple Form"
Exit Sub
End If

'Copying the data entered in Simple Form to DATA Sheet
DATA.Cells(LastRow + 1, 1).Value = txtName.Value
DATA.Cells(LastRow + 1, 2).Value = txtCity.Value
DATA.Cells(LastRow + 1, 3).Value = txtCountry.Value

'Display a Message
MsgBox "Form has been submitted Successfully", , "Simple Form"

'Resetting all the fields to blank
btnReset_Click

End Sub

-------------------------------------------------------------------------------------------------------------

Double click on Reset Button and add below code.

Private Sub btnReset_Click()

'Resetting all the fields

txtName.Value = ""

txtCity.Value = ""

txtCountry.Value = ""

End Sub

--------------------------------------------------------------------------------------------------------------

Double click on Close Button and add below code.

Private Sub btnClose_Click()

'Hiding Form

SimpleForm.Hide

End Sub

---------------------------------------------------------------------------------------------------------------

Now Go to Insert and Click on Module.

Sub OpenForm()

'Open a Form

SimpleForm.Show

End Sub

---------------------------------------------------------------------------------------------------------------

Now in the DATA Sheet add Shape by going to Insert => Shape.

Right Click on Selected Sheet and Select Assign Macro.

Select Open Form

Now to Start form click on Simple Form.

See it in Action.