Making An Advanced Userform

Let’s make together an UserForm.

excel userform

The column headers of the worksheet as follows :

– First Name

– Company

– Address

– City

– Country

– State


– Phone

– Fax

– Email

– Web

– Estimated Revenue

First we have created 12 units text boxes that to enter data into this columns. There are labels on their side.


We put 4 units main buttons. buttons and their codes are as follows:

userformbutton Codes of this buttons :

Private Sub CommandButton1_Click() ‘SAVE Button
Dim sonsat As Integer

If TextBox1.Value = “” Then
MsgBox “Please enter a First Name.”, vbExclamation
Exit Sub
End If
If TextBox2.Value = “” Then
MsgBox “Please enter a Company Name.”, vbExclamation
Exit Sub
End If
If TextBox3.Value = “” Then
MsgBox “Please enter an Adress.”, vbExclamation
Exit Sub
End If
If TextBox10.Value = “” Then
MsgBox “Please enter an Email.”, vbExclamation
Exit Sub
End If
If TextBox12.Value = “” Then
MsgBox “Please enter Estimated Revenue.”, vbExclamation
Exit Sub
End If

If Not IsNumeric(TextBox12.Text) Then
MsgBox “Please enter a Numeric Value.”, vbExclamation
Exit Sub
End If
sonsat = Sheets(“Data”).[a65536].End(3).row + 1
Cells(sonsat, 1) = TextBox1
Cells(sonsat, 2) = TextBox2
Cells(sonsat, 3) = TextBox3
Cells(sonsat, 4) = TextBox4
Cells(sonsat, 5) = TextBox5
Cells(sonsat, 6) = TextBox6
Cells(sonsat, 7) = TextBox7
Cells(sonsat, 8) = TextBox8
Cells(sonsat, 9) = TextBox9
Cells(sonsat, 10) = TextBox10
Cells(sonsat, 11) = TextBox11
Cells(sonsat, 12) = TextBox12
MsgBox “Registration is successful”
ListBox1.List = Sheets(“Data”).Range(“a2:l” & [a65536].End(3).row).Value ‘For refresh listbox
TextBox14.Value = ListBox1.ListCount
End Sub


Private Sub CommandButton3_Click() ‘ DELETE Button
Dim sil As Long
If ListBox1.ListIndex = -1 Then
MsgBox “Choose an entry”, vbExclamation
End If
If ListBox1.ListIndex >= 0 Then
cevap = MsgBox(“Entry will be deleted. … Are you sure ?”, vbYesNo)
If cevap = vbYes Then
sil = ListBox1.ListIndex + 2
End If
End If

For a = 1 To 12
Controls(“textbox” & a) = “”

ListBox1.List = Sheets(“Data”).Range(“a2:l” & [a65536].End(3).row).Value
TextBox14.Value = ListBox1.ListCount
End Sub

Private Sub CommandButton4_Click() ‘CLEAR Button
Dim del As Control
For Each del In UserForm1.Controls
If TypeName(del) = “TextBox” Or TypeName(del) = “ComboBox” Then
del.Text = “”
ElseIf TypeName(del) = “ListBox” Then
del.Value = “”

End If
Next del
TextBox14.Value = ListBox1.ListCount
End Sub

We’ve added a listbox to bottom of the form.

userform-listboxWe fill the listbox with the following code:

ListBox1.List = Sheets(“Data”).Range(“a2:l” & [a65536].End(3).row).Value

We’ve added on listbox a textbox that it views total entries .

totalentriesIt’s code: TextBox14.Value = ListBox1.ListCount

Also ,next record ,previous record,first record and last record showing buttons was added.


Search section consists of textbox, combobox ,two buttons and label.The combobox was added into search section that for to choose search column.Search results as number will be displayed in this label.




I made some updates on the template codes.The new version of the template :

excel userform



  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: