Auto Filter Data from Multiple Worksheets Using only One Combobox

[rorobear]

Hello All,
If someone could help out with this code, I’d be forever grateful. I have a userform with a listbox, and two comboboxes. The first combobox (combobox1) let’s the user select from one of the many worksheets in the workbook. The second combobox (combobox2) is supposed to autofilter the data in the worksheet from a particular column as well as in the listbox. The problem I have is two-fold. (1) When the filter is applied the listbox clears, and (2) I’m only able to filter data in the first worksheet. How do I get the listbox to display the filtered data and also, how can I filter the data based on the sheet selected as apposed to just the first worksheet. The codes are below and I’ve also attached the workbook. Any assistance is greatly appreciated.
Roro


Private Sub UserForm_Initialize()
Dim i As Integer
For i = 2 To Sheets.Count
Me.ComboBox1.AddItem Sheets(i).Name
Next i
Me.ListBox1.ColumnWidths = "5;70;70;80;80;80;150;55;70;150;85"
Me.ComboBox2.List = Array("ALPHA", "BRAVO", "CHARLIE")
End Sub


Private Sub Combobox1_Change()
Dim LR As Long, LC As Long
Sheets(ComboBox1.Value).Activate
Me.ListBox1.Clear
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
With Sheets(Me.ComboBox1.Value)
LR = .Range("B" & Rows.Count).End(xlUp).Row
LC = .ListObjects(1).ListColumns.Count + 1
With .Range("A2", .Cells(LR, LC))
Me.ListBox1.ColumnCount = .Columns.Count
Me.ListBox1.List = .Value
End With
End With
End Sub


Private Sub ComboBox2_Change()
Dim database(1 To 100, 1 To 10)
Dim my_range As Integer
Dim column As Byte
On Error Resume Next
Sheet2.Range("B4").AutoFilter field:=5, Criteria1:=Me.ComboBox2.Value
For i = 2 To Sheet2.Range("B10000").End(x1up).Row
If Sheet2.Cells(i, 1) = Me.ComboBox2 Then
my_range = my_range + 1
For column = 1 To 10
database(my_range, column) = Sheet2.Cells(i, column)
Next column
End If
Next i
Me.ListBox1.List = database
End Sub

[snb]

Structuring precedes coding.

[rorobear]

hi snb,

thanks for the response, this is exactly what i was trying to achieve. however, now the other features in the workbook, such as the listbox click event used to populate the textboxes for editing and deleting don't work. i'd like to keep those features. thank you.


Private Sub ListBox1_Click()
For I = 1 To NbCol
tmp = Me.ListBox1.Column(I - 1)
If Not IsError(tmp) Then Me("textbox" & I) = tmp
Next I
Label11 = ListBox1.ListIndex + 1
End Sub

[snb]

See

[rorobear]

snb,

soo close! I added the "update" and "delete" code with suggested changes. the "delete works fine, but the "update" doesn't, rather it just create a duplicate copy of what i'm trying to update I've attached the revised workbook. thanks for your patience on this.

[snb]

I posted the workbook primarily to illustrate the method (the 'how') of creating a userform as UI for a 'database'. (see also: https://www.snb-vba.eu/VBA_Userform_in_database_en.html).
I am more curious what you think of this 'how' instead of the results. I thought you might be interested in improving your VBA skills.

[rorobear]

was trying to achieve both. I do appreciate your time and I hope I haven't inconvenience you too much.