rorobear
10-27-2020, 05:52 PM
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
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