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 Integer
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. Odette
    September 16, 2015 at 4:20 am

    I used your form and it works well but when I do a search and edit from results it overwrites info on a different cell in the worksheet. Please help. Thanks.

    • netmerkez
      September 16, 2015 at 3:23 pm

      okey , I will review it

    • Mirza Waqas
      September 23, 2015 at 6:45 pm

      When i try to add more columns it gives error like, could not get column property invalid argument
      , if possible kindly add more columns and advise how to add i shall b very thankful

      • netmerkez
        September 30, 2015 at 8:23 am

        Error is normal.Coding is done according to the number of columns in the template.How many would you like column?

  2. dared3vil
    October 9, 2015 at 6:43 am

    could you show me how to add how many columns do I want. At least to add e more columns, what do I have to modify

  3. michiel
    October 19, 2015 at 10:21 pm

    best Yazin, i really like the userform and i try to change is to a 20 colum data sheet but itry and try but didnt work can you ecplain how to change code or have a fill for me

  4. michiel
    October 26, 2015 at 8:52 pm

    i need 20 column can u help me ?

    • netmerkez
      October 30, 2015 at 12:13 pm

      ok ,I will make it

  5. michiel
    October 30, 2015 at 10:17 pm

    ty in advance im just starting vba and learned good things already try to make the 20 row form myself based up on your form and code but hope to learn more when i can see it really can work where the code is changing ty !

  6. gezenek
    November 4, 2015 at 1:38 pm

    Merhaba, bu user formu kullanıyorum ama şöyle bir problem yaşıyorum. Arama kutusundan bir proje bulup değişiklik yapınca yeni kayıt açıyor.

  7. netmerkez
    November 22, 2015 at 2:42 pm

    I updated this template – 22/11/2015

  8. February 21, 2016 at 7:53 am

    Please make more then 15 colum

    • netmerkez
      February 25, 2016 at 12:54 pm

      okey

  9. molly gerhardt
    July 28, 2016 at 1:18 am

    Hi — i’m trying to copy my data to your example sheet, but every time i paste in my unformatted zipcode data into your zipcode column it screws up the formatting — it makes all the zipcode data left-adjusted and followed by a paragraph symbol.

    i think your example is wonderful, but i can’t see how i can use it until i can copy my own data to your example. Can you help me please?

    • netmerkez
      July 28, 2016 at 6:49 am

      How is your zip code format?
      I checked ,cell format of zip code’s column is : “General”.

  10. molly gerhardt
    July 29, 2016 at 1:05 am

    Thanks for the reply, Temmuz. I managed to get around that very weird issue, btu I now have something even worse with the search box.

    I entered my own data and had to change a few column names. “Company” is now “Last Name”. So, i edited the search box code (I am not a programmer), and changed the references from “Company” to “Last Name”. The search results work fine, but when the search is complete, it has searched on “First Name” in the original sheet (not the form, which is fine). So, if I am looking for “Wendy Zyckoff” in the Last Name, it may instead search the sheet for “Wendy” in First Name, rather than searching for Wyckofff in the Last Name.Can you help me restore the proper search functionality?

  11. molly gerhardt
    July 29, 2016 at 2:22 am

    One more fact about that issue. I pasted my data into what I think is your original sheet, and i did not modify any code. Yet the same weird problem happened. In fact, in your old sheet, when i click on a row in the form, it searches the sheet for First Name rather than Company (column B). How weird is that?

  12. molly gerhardt
    July 29, 2016 at 4:03 pm

    my apologies for incorrectly calling you Temmuz, when your name is surely Netmerke. I don’t speak Turkish. Sorry.

  13. molly gerhardt
    July 29, 2016 at 4:29 pm

    It turns out that this is actually a bug in your program. You can verify this, just as I did.

    In your sheet, I added a row at the very bottom with First Name “Lea Picado” and with the Company field value of “TEST TEST”. And sure enough, when I searched for “TEST TEST” ins the Company field, the form did give me the proper row for Lea Picado TEST TEST, but the spreadsheet itself incorrectly scrolled to the earlier “Lea Picado” record.

    It’s a bug. One I hope you can fix.

  14. Fernando
    October 23, 2016 at 4:33 pm

    Hi Netmerke,
    tnxs for your code, Its very nice,
    I have a doubt, how can we sort the database alphabetically in your listbox?

    • netmerkez
      October 24, 2016 at 5:31 pm

      Try these codes :

      Function Sirala(Liste)
      Dim i As Long, j As Long, x As Variant
      For i = 0 To UBound(Liste) – 1
      For j = i + 1 To UBound(Liste)
      If StrComp(Liste(i, 0), Liste(j, 0)) = 1 Then
      x = Liste(i, 0)
      Liste(i, 0) = Liste(j, 0)
      Liste(j, 0) = x
      End If
      Next j
      Next i
      Sirala = Liste
      End Function

      Private Sub UserForm_Initialize()
      ListBox1.List = Sheets(“Data”).Range(“A2:l” & [a65536].End(3).row).Value
      Liste = ListBox1.List
      ListBox1.List = Sirala(Liste)
      End Sub

  15. ahernen
    November 8, 2016 at 2:23 am

    Hi Netmerke
    I love your userform! Thanks. I have used it to make a database that use at work. I see from an earlier post that you added colums for someone but I see no link to a file or instructions on how to add columns. Can you help? I would like to have 24 columns if possible. Thanks again.

    • netmerkez
      November 8, 2016 at 9:56 am

      ok , I will deal with the subject

  16. Alom Ambia
    November 14, 2016 at 11:39 am

    Hi Netmerke,

    Is there anyway the userform can be called from another sheet, without activating the data sheet? I want to keep the Data sheet hidden.
    Your help is much appreciated.

    • netmerkez
      November 16, 2016 at 10:29 am

      The sheet will be hidden but the userform will get the data from the hidden page . Is that so?

  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: