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 .
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
