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.