Making an Advanced Userform

Let’s make together an UserForm.

The column headers of the page as follows :

– First Name

– Company

– Address

– City

– Country

– State

– ZIP

– Phone

– Fax

– Email

– Web

– Estimated Revenue

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

textbox

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
TextBox1.SetFocus
Exit Sub
End If
If TextBox2.Value = “” Then
MsgBox “Please enter a Company Name.”, vbExclamation
TextBox2.SetFocus
Exit Sub
End If
If TextBox3.Value = “” Then
MsgBox “Please enter an Adress.”, vbExclamation
TextBox3.SetFocus
Exit Sub
End If
If TextBox10.Value = “” Then
MsgBox “Please enter an Email.”, vbExclamation
TextBox10.SetFocus
Exit Sub
End If
If TextBox12.Value = “” Then
MsgBox “Please enter Estimated Revenue.”, vbExclamation
TextBox12.SetFocus
Exit Sub
End If

If Not IsNumeric(TextBox12.Text) Then
MsgBox “Please enter a Numeric Value.”, vbExclamation
TextBox12.SetFocus
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
Sheets(“Data”).Rows(sil).Delete
End If
End If

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

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.

nextpreviousbutton

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.

userformsearch

——————————————————————————————————————————————

22.11.2015 

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

TO DOWNLOAD TEMPLATE CLICK HERE

 

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: