Userform Listbox type and search

kabirr

New Member
Joined
Jul 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I m new to vba. Tried to make a userform to show data more than 70000 above. Listbox"ListBox1" with 3 column count get data from table"Table1" with 3 column. On textbox"TextBox4" change shows duplicated rows in listbox. Lost huge time to figure correct way but no result helped. Please help me with this if anybody has idea for same.

Table1 has unique data i.e. each row is unique.
Table2 has button to show userform.
Listbox "ListBox1" to show S1 data on S2 sheet.
button b1 save worksheet or workbook don't know what
button b2 get data in texbox 1 2 3 on listbox1 select click from table1
Want to use table instead of worksheet range format.
also wanted to filter second column on listbox1 and data must always shown .


VBA Code:
Private Sub b1_Click()

    Me.TextBox1 = ""
    Me.TextBox2 = ""
    Me.TextBox3 = ""
    Me.TextBox4.SetFocus
    ActiveWorkbook.Save
    
End Sub

Private Sub b2_Click()
    
    Sheet2.Activate
    Dim rng As Range
    Set rng = ActiveSheet.ListObjects("Table2").Range
    Dim lastrow As Long
        lastrow = rng.Find(what:="*", _
                           After:=rng.Cells(1), _
                           lookat:=xlPart, _
                           LookIn:=xlFormulas, _
                           Searchorder:=xlByRows, _
                           Searchdirection:=xlPrevious, _
                           MatchCase:=False).Row
    
    rng.Parent.Cells(lastrow + 1, 1).Value = TextBox1.Value
    rng.Parent.Cells(lastrow + 1, 2).Value = TextBox2.Value
    rng.Parent.Cells(lastrow + 1, 3).Value = TextBox3.Value

    Me.TextBox1.SetFocus

End Sub

Private Sub ListBox1_Click()

    Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
    Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
    Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)

End Sub

Private Sub TextBox4_Change()

    Dim i As Long
    Me.ListBox1.Clear
    For i = 2 To Sheet1.Range("A70000").End(xlUp).Row ("i want to change source as table S1 not range")
    For X = 1 To Len(Sheet1.Cells(i, 2))
    a = Me.TextBox4.TextLength
    If LCase(Mid(Sheet1.Cells(i, 2), X, a)) = Me.TextBox4 And Me.TextBox4 <> "" Then
    Me.ListBox1.AddItem Sheet1.Cells(i, 1)
    Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 2)
    Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Sheet1.Cells(i, 3)
    End If
    Next X
    Next i


End Sub

Private Sub UserForm_Activate()

    Worksheets("S2").Activate
    Me.TextBox1 = ""
    Me.TextBox2 = ""
    Me.TextBox3 = ""
    TextBox5 = ListBox1.ListCount
    Me.TextBox4.SetFocus

End Sub
 

Attachments

  • Screenshot 2021-07-28 140434.png
    Screenshot 2021-07-28 140434.png
    18.8 KB · Views: 10

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

kabirr

New Member
Joined
Jul 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I m new to vba. Tried to make a userform to show data more than 70000 above. Listbox"ListBox1" with 3 column count get data from table"Table1" with 3 column. On textbox"TextBox4" change shows duplicated rows in listbox. Lost huge time to figure correct way but no result helped. Please help me with this if anybody has idea for same.

Table1 has unique data i.e. each row is unique.
Table2 has button to show userform.
Listbox "ListBox1" to show S1 data on S2 sheet.
button b1 save worksheet or workbook don't know what
button b2 get data in texbox 1 2 3 on listbox1 select click from table1
Want to use table instead of worksheet range format.
also wanted to filter second column on listbox1 and data must always shown .



VBA Code:
Private Sub b1_Click()

    Me.TextBox1 = ""
    Me.TextBox2 = ""
    Me.TextBox3 = ""
    Me.TextBox4.SetFocus
    ActiveWorkbook.Save
   
End Sub

Private Sub b2_Click()
   
    Sheet2.Activate
    Dim rng As Range
    Set rng = ActiveSheet.ListObjects("Table2").Range
    Dim lastrow As Long
        lastrow = rng.Find(what:="*", _
                           After:=rng.Cells(1), _
                           lookat:=xlPart, _
                           LookIn:=xlFormulas, _
                           Searchorder:=xlByRows, _
                           Searchdirection:=xlPrevious, _
                           MatchCase:=False).Row
   
    rng.Parent.Cells(lastrow + 1, 1).Value = TextBox1.Value
    rng.Parent.Cells(lastrow + 1, 2).Value = TextBox2.Value
    rng.Parent.Cells(lastrow + 1, 3).Value = TextBox3.Value

    Me.TextBox1.SetFocus

End Sub

Private Sub ListBox1_Click()

    Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
    Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
    Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)

End Sub

Private Sub TextBox4_Change()

    Dim i As Long
    Me.ListBox1.Clear
    For i = 2 To Sheet1.Range("A70000").End(xlUp).Row ("i want to change source as table S1 not range")
    For X = 1 To Len(Sheet1.Cells(i, 2))
    a = Me.TextBox4.TextLength
    If LCase(Mid(Sheet1.Cells(i, 2), X, a)) = Me.TextBox4 And Me.TextBox4 <> "" Then
    Me.ListBox1.AddItem Sheet1.Cells(i, 1)
    Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 2)
    Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Sheet1.Cells(i, 3)
    End If
    Next X
    Next i


End Sub

Private Sub UserForm_Activate()

    Worksheets("S2").Activate
    Me.TextBox1 = ""
    Me.TextBox2 = ""
    Me.TextBox3 = ""
    TextBox5 = ListBox1.ListCount
    Me.TextBox4.SetFocus

End Sub
On every first input the duplicate record shown in listbox, as keep typing to match the duplicate rows becomes unique list in listbox
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,570
Office Version
  1. 365
Platform
  1. Windows
Hi, @kabirr. Welcome to the Forum
See if this discussion helps:
You can download a sample workbook from the link in post #3, but replace the code with the final code in post #9.
 
Solution

kabirr

New Member
Joined
Jul 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi, @kabirr. Welcome to the Forum
See if this discussion helps:
You can download a sample workbook from the link in post #3, but replace the code with the final code in post #9.
I found a new code which in every cellType and search
 

kabirr

New Member
Joined
Jul 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I found a new code which in every cellType and search
it's good but when i replace the range with my table named 'Table1' and column count is 20. the code shows error 'Sub script out of range' at some line (shown in file attached'. I want make rows increase as it is a transaction table with increasing rows. Please guide me.
 

kabirr

New Member
Joined
Jul 28, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
it's good but when i replace the range with my table named 'Table1' and column count is 20. the code shows error 'Sub script out of range' at some line (shown in file attached'. I want make rows increase as it is a transaction table with increasing rows. Please guide me.
Here the image
 

Attachments

  • 1.jpg
    1.jpg
    44.9 KB · Views: 12
  • 1.jpg
    1.jpg
    65.6 KB · Views: 12

Forum statistics

Threads
1,148,415
Messages
5,746,546
Members
424,032
Latest member
pochie2741

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top