VBA The Updated Userform

Excel Advanced Userform Example


         🔧 I made some changes in the UserForm codes that  I've done it before :


 The copying the filtered data into Page 2
 While entering data if there are duplicate values in a column ,can be warning with msgbox.
 The listbox  has got 12 columns.

It's other features :

Data Adding

 Data Delition

 Data Update

 Searching with The Userform 


 The showing the number of the found data in the search process on the label.

 Progress Bar Feature

 Scrolling the Listbox with Spin Buttons

 Next ,Previous ,First ,Last Recording Buttons



excel vba userform

With Userform, search process can be made  in 4 different columns (name, company, city, estimated revenue columns). Codes that we used to search :
Private Sub CommandButton5_Click() 'Search Button
Dim sat, s As Long
Dim deg1, deg2 As String
Sheets("Data").Activate
Application.ScreenUpdating = False
If TextBox13.Value = "" Then
MsgBox "Please enter a value", vbExclamation
TextBox13.SetFocus
Exit Sub
End If
If ComboBox1.Value = "" Or ComboBox1.Value = "-" Then
MsgBox "Choose a Filter Field", vbExclamation
ComboBox1.SetFocus
Exit Sub
End If
For a = 1 To 12 ' Clear textboxes(1-12)
Controls("textbox" & a) = ""
Next
With ListBox1
.Clear
.ColumnCount = 12
.ColumnWidths = "92;140;110;65;65;35;40;65;65;115;150;65"
End With
Call Main 'Progress Bar

deg2 = TextBox13.Value
Select Case ComboBox1.Value
Case "First Name"
For sat = 2 To Cells(65536, "a").End(xlUp).row
Set deg1 = Cells(sat, "a")
If UCase(deg1) Like UCase(deg2) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next

Case "Company"
For sat = 2 To Cells(65536, "b").End(xlUp).row
Set deg1 = Cells(sat, "b")
If UCase(deg1) Like UCase(deg2) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next

Case "City"
For sat = 2 To Cells(65536, "d").End(xlUp).row
Set deg1 = Cells(sat, "d")
If UCase(deg1) Like UCase(deg2) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next


Case "Estimated Revenue"
For sat = 2 To Cells(65536, "l").End(xlUp).row
Set deg1 = Cells(sat, "l")
If deg1 Like deg2 & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next
End Select
Application.ScreenUpdating = True
Label15.Caption = ListBox1.ListCount

End Sub


43 comments:

  1. How to edit above sample file. i need to update my own record with new project

    ReplyDelete
    Replies
    1. You can easily apply to your own projects. If the quantity of column is different ,some changes may need to make .

      Delete
    2. Sir Can you give a sample of that Template. please sir i want that Template to my Internship Task..

      Delete
  2. i am not able to download duo to Link Expired
    can you mail me

    ReplyDelete
    Replies
    1. Links are correct.
      http://adf.ly/1SDAdD or https://adf.ly/1SDAdD

      Delete
    2. Private Sub ListBox1_Click()
      Dim say As Long, a As Byte

      For a = 0 To 11
      Controls("textbox" & a + 1) = ListBox1.column(a)
      Next

      Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
      say = ActiveCell.row
      Sheets("Data").Range("A" & say & ":L" & say).Select
      TextBox15 = ListBox1.ListIndex + 1
      End Sub


      Hi, I want to add "combobox" and "dtpicker" in control box along with "textbox". How to do this?

      Delete
  3. Private Sub ListBox1_Click()
    Dim say As Long, a As Byte

    For a = 0 To 11
    Controls("textbox" & a + 1) = ListBox1.column(a)
    Next

    Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
    say = ActiveCell.row
    Sheets("Data").Range("A" & say & ":L" & say).Select
    TextBox15 = ListBox1.ListIndex + 1
    End Sub


    Hi, I want to add "combobox" and "dtpicker" in control box along with "textbox". How to do this?

    ReplyDelete
  4. After Adding ComboBox It gives me error on this line:

    Controls("textbox" & a + 1) = ListBox1.column(a)

    please help me

    ReplyDelete
    Replies
    1. I have been getting an error on that line for 2 days now and i cant figure out what i need to change for that controls to work what is "textbox" & a + 1 ????

      Delete
    2. can you please email me at jeanesky@yahoo.com or call me 347-481-1146 I need help please

      Delete
    3. have you found the solution ???

      Delete
    4. I just came into this great userform but got stuck on the textbox control problem. Had anyone solved this, please be kind enough to show the adjusted codes. Thank you.

      Delete
  5. Hi,
    This is a great project...I am creating something similar and I want to bring in data from Multiple worksheet into Listbox1. How do i go about doing this? I could combine data into new sheet and have the listbox1 get data from there but, I want to avoid having additional sheet.
    Thanks for your help.

    ReplyDelete
  6. Hi,
    Can you please help, I like the advanced userform, however when Searching with The Userform it only finds first word in a column and ignors the rest, I want to search all word in row across column, can this be done, Thank you

    ReplyDelete
  7. Hi,
    first of all thanks you very much,
    i dont understand whats its means --> [a2].select

    Private Sub CommandButton1_Click()
    Dim LastRow As Long
    [a2].Select
    LastRow = ActiveCell.End(xlDown).row
    Sheets("SelectedData").Range("A2:L" & LastRow).Clear
    End Sub

    [a2].select -->> SelectedData.select????????

    ReplyDelete
    Replies
    1. A2 cell are selected with this code : [a2].select
      Or , it can be by this way: Range("A2").Select

      Delete
    2. Hi Mr kadir,

      Appreciate if you could explain me what does "End(3).row" from this code ->Sheets("Data").Range("a2:l" & [a65536].End(3).row).Value ?

      Delete
    3. Hi,
      It is to find the last filled cell in column.

      Delete
  8. Jacquelyn (Canada)August 4, 2016 at 2:59 AM

    Hello,

    Your file has been so helpful. Thank you very much for sharing!

    I have the same question as Akshay above. How can I have several text boxes AND combo boxes to this and make it work?

    Private Sub ListBox1_Click()
    Dim say As Long, a As Byte

    For a = 0 To 11
    Controls("textbox" & a + 1) = ListBox1.column(a)
    Next

    Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
    say = ActiveCell.row
    Sheets("Data").Range("A" & say & ":L" & say).Select
    TextBox15 = ListBox1.ListIndex + 1
    End Sub


    After Adding ComboBoxes, I get an error on this line too:

    Controls("textbox" & a + 1) = ListBox1.column(a)


    Thank you very, very much for your help.

    ReplyDelete
    Replies
    1. For a = 0 To 11
      Controls("textbox" & a + 1) = ListBox1.column(a)
      Next

      The above codes is for data transferr (to fill text boxes) to the text boxes when clicked on the listbox.
      which will be filled the added combobox with items ? You need add to codes.

      Delete
    2. Hi Catherine. Were you able to solve the problem? My userform includes textboxes, comboboxes and DtPickers.
      Thank you.
      Marcia

      Delete
  9. HI,
    Id it possible to use this new function youi created "Searching A Value Across An Entire Workbook With Userform" in this Userform? I need to create multiple Sheets and send data to them but, I can not update the entry later.

    Thank you very much for your help!

    ReplyDelete
    Replies
    1. I have 33 division that i get paper work for and need to send to multiple people for review and sometimes i have revisions of the same documents (101.1r. 101.2r...). Just need to keep them all separate. Hope this helps!

      Thanks You.

      Delete
  10. my quantity of column is 15. I had added accordingly.

    But Change, Delete, Scrolling the Listbox with Spin Buttons are not working. I can't figure out why.

    When I click on the listbox, the listbox item are not displaying in textboxes. Selected Data also cannot copy to next page.

    This userform is absolute match for my project.
    Hope I could trouble shoot and pass to my colleague.

    Thank you and appreciate very much for your help.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. I made a template you wanted .
      Take a look at here :
      New Updated Userform : 15 Column & More Faster Search Method

      https://merkez-ihayat.blogspot.com/2016/11/new-updated-userform-15-column-more.html

      Delete
  11. Very nice Userform, very nicely done.
    I am using it already but I am trying to change 2 textboxes in exchange for comboboxes and I want to select a value in combobox1 and populate combobox2 based on the combobox1 selection, I have another userform that already does that but I haven't been able to figure out what parts of the code are needed to get this userform to do the same.
    I don't know how to put codes together I am just a copy and paste type of guy :)
    all help will be appreciated.

    ReplyDelete
  12. Hi I'm Trying to use this Code
    Private Sub ListBox1_Click()
    Dim say As Long, a As Byte

    For a = 0 To 11
    Controls("textbox" & a + 1) = ListBox1.column(a)
    Next

    Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
    say = ActiveCell.row
    Sheets("Data").Range("A" & say & ":L" & say).Select
    TextBox15 = ListBox1.ListIndex + 1
    End Sub

    To let my ListBox selection also select the Sheet row like the sample on this UserForm, However I keep getting an error from Excel on this line

    Controls("textbox" & a + 1) = ListBox1.column(a)

    what is "textbox" and why cant I find anything that relates to it, can anyone tell me how to fix this.

    ReplyDelete
    Replies
    1. For a = 0 To 11
      Controls("textbox" & a + 1) = ListBox1.column(a)
      Next

      The above codes(loop) are for to fill text boxes (TextBox1,TextBox2,TextBox3,TextBox4,TextBox5,TextBox5,TextBox6,TextBox7,TextBox8,TextBox9,TextBox10,TextBox11) when clicked on the listbox.
      What are the names of the text boxes in your example? (TextBox1,TextBox2,TextBox3 etc.)

      Delete
  13. My listbox shows cells from Sheet2 previously submitted A2:F2 first row and shows every row after, I don't want the Form To populate my Texboxes because I have new info on them waiting to be submitted, I only want to be able to see the info for reference, now the only thing I want to do is if I select a row in list for it to select the row on the sheet and only populate the text from column G into another listbox, like I said I can see A:F on first listbox that holds all small entries, but the info on G is comments, so to big to fit I want it to select the row that I click on ex: Row 5 is selected on Listbox1 it selects A5:F5 on Sheet2 and Sends Comments From G5 to ListBox2

    ReplyDelete
  14. very nice userform. I modify the form which can duplicate the first name with different company. Once I update the selected item, the listbox remain and highlight the record with the same name but the top record (not the last updated record). For example I update the record name "KK" in row 200, but after that it highlight the record it row 20 name "KK"
    can help to solve please....

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Dear Kadr Leyn
    A few days ago I visited your blog and downloaded all the work files here, and I found everything I was looking for.
    I just returned today in order to extend my thanks and appreciation to you
    good luck ♥

    ReplyDelete
  17. Hello Master Kadr Leyn, this example is not possible with RowSourse, (ListBox1.ColumnHeads)

    e tried to modify it for rowsource, it gives error in the option save, change, delete, clear, search, please is important header thank you very much from peru city

    thank you very much for reading comment, please see the heads, Sorry my English is not very good I'm a novice

    ReplyDelete
  18. Excuse me master I forgot to say that it is for 38 rowsource columns thanks from peru city

    ReplyDelete
  19. Hi do you still have a link that works to download the code?
    Thanks

    ReplyDelete
  20. Hi i have done similar program to do QC. How do i highlight the changes done in userform to the cell with green color.

    1st - I search records and made the changes to three textbox and one combobox from 7 text box and 5 combobox. Only 4 box i did the changes and up to here is ok.

    2nd - When i click the update button, i want the updated cells background color to change to green so that i can track the changes done.

    I know it may involve if then statement but i am lost for almost 4 weeks now.

    ReplyDelete
  21. dear sir , how can i show the headers in the list box..

    ReplyDelete
  22. Hi,
    the different solutions can be found with VBA codes (using arrays) or labels can be added for each column on the listbox.

    ReplyDelete
  23. Tips to newbies like me on how to make this great great great userform work on your database.
    1. Clear your existing data of all data validations, condtional formattings, comments and the like
    2. If your data is stored in a Table, convert the table to data range
    3. Copy and paste your data including the headings into the data sheet of the Advanced User Form workbook
    4. The names of the fields in the user form should be TextBox1, TexBox2 and so on. Even if your control is a combo box or date picker, the name in the property should be TextBox3..... not the usual cboName, dtpName
    5. Set the check box property of the date picker field to True
    6. If you have more than 12 data fields, say 30, change the name of TextBox13 of the Advance User Form to TextBox31, the Textbox14 to TextBox32 and TextBox15 to TextBox33
    7. It is good practice to write the actual name of TextBox1 in the code right after but marked with apostrophe unless your memory makes this procedure unnecessary
    It took me about a week to find the causes of run time errors. Now I have a beautiful and graceful form. Big thanks to our Good Samaritan Mr. Kadr.

    ReplyDelete