Selected Answer
Hi again Kalil
The problem is that the procedure Private Sub UserForm_Activate runs when UserForm1 is launched but initially there is no value in ComboBox5 so VBA cannot set the worksheet ws. (That isn't a problem if the code is run later and ComboBox5 has a value).
To get around that, I added some code (in bold below) and corrected the line where you define the data area:
Private Sub UserForm_Activate()
Dim dic1 As Object, dic2 As Object, dic3 As Object, dic4 As Object
Dim i As Long
Dim ws As Worksheet
If ComboBox5.Value <> "" Then
Set ws = Sheets(ComboBox5.Value)
Else
Set ws = Sheets(1)
End If
Set dic1 = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")
Set dic3 = CreateObject("Scripting.Dictionary")
Set dic4 = CreateObject("Scripting.Dictionary")
'Load info from sheet SH into a variable, named a
'a = Sheets("ws").Range("A1:G" & Sheets("ws").Range("G" & Rows.Count).End(3).Row).Value
a = ws.Range("A1:G" & ws.Range("G" & Rows.Count).End(3).Row).Value
'Set column widths of the Listbox
With ListBox1
.RowSource = ""
.ColumnWidths = "100;100;100;130;80,80,80"
.ColumnCount = 7
.Font.Size = 10
End With
'Add the values of the range (a) to the various
For i = 2 To UBound(a, 1)
If a(i, 2) <> "" Then dic1(a(i, 2)) = Empty
If a(i, 3) <> "" Then dic2(a(i, 3)) = Empty
If a(i, 4) <> "" Then dic3(a(i, 4)) = Empty
If a(i, 6) <> "" Then dic4(a(i, 6)) = Empty
Next
'Put the dictionaries/lists as source of the Comboboxes
ComboBox1.List = dic1.keys
ComboBox2.List = dic2.keys
ComboBox3.List = dic3.keys
ComboBox4.List = dic4.keys
End Sub
Also the code below is written as an incomplete If... Then / End If block and can produce an error:
Private Sub ComboBox5_Change()
If Sheets(ComboBox5.Value) <> "" Then
Call FilterData
End Sub
but it just needs to be replaced with this (where the amended If ... Then is a single line)
Private Sub ComboBox5_Change()
If ComboBox5.Value <> "" Then Call FilterData
End Sub
These changes are made in the first attached, revised file.
This still isn't ideal in my opinion- it would be better to disable the "filtering" ComboBoxes until CB5 has a value (e.g. you can launch the form and set ComboBox1 to KMLL to get filtered results but you don't know which sheet you have filtered).
That would take more effort so I hope this fix helps to to progress- if so, please remember to mark this Answer as Selected.
Revision #1 29 November 2024
To address the issue of changing source sheet, at the top of the UserForm code I added a new variable:
Dim a As Variant, FirstCB5 As Boolean
This isn't set intitally so when CB5 is first changed (during UserForm_Activate), the revised CB5 module below (and in second attached file) just sets that variable but nothing more. Otherwise it refreshes the data (in variant a):
Private Sub ComboBox5_Change()
' see if this is the first UF activation (where FirstCB isn't set)
If ComboBox5.Value <> "" And FirstCB5 = False Then
FirstCB5 = True
Exit Sub
' if not, refresh UF for selected sheet
Else
Call UserForm_Activate
Call FilterData
End If
End Sub
Now the Activate code below sets CB5 (so the view makes sense) and the ComboBox dropdowns are cleared before being replaced (so they reflect the new source sheet)- see changes in bold:
Private Sub UserForm_Activate()
Dim dic1 As Object, dic2 As Object, dic3 As Object, dic4 As Object
Dim i As Long
Dim ws As Worksheet
' check if CB5 is set
If ComboBox5.Value <> "" Then
Set ws = Sheets(ComboBox5.Value)
Else
Set ws = Sheets(1)
' show first sheet in CB5 (will trigger ComboBox5_Change)
ComboBox5.Value = ws.Name
' set variable
FirstCB5 = True
End If
Set dic1 = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")
Set dic3 = CreateObject("Scripting.Dictionary")
Set dic4 = CreateObject("Scripting.Dictionary")
'Load info from sheet SH into a variable, named a
a = ws.Range("A1:G" & ws.Range("G" & Rows.Count).End(3).Row).Value
'Set column widths of the Listbox
With ListBox1
.RowSource = ""
.ColumnWidths = "100;100;100;130;80,80,80"
.ColumnCount = 7
.Font.Size = 10
.Clear
End With
'Add the values of the range (a) to the various
For i = 2 To UBound(a, 1)
If a(i, 2) <> "" Then dic1(a(i, 2)) = Empty
If a(i, 3) <> "" Then dic2(a(i, 3)) = Empty
If a(i, 4) <> "" Then dic3(a(i, 4)) = Empty
If a(i, 6) <> "" Then dic4(a(i, 6)) = Empty
Next
' clear CB dropdowns
For i = ComboBox1.ListCount - 1 To 0 Step -1
ComboBox1.RemoveItem i
Next i
For i = ComboBox2.ListCount - 1 To 0 Step -1
ComboBox2.RemoveItem i
Next i
For i = ComboBox2.ListCount - 1 To 0 Step -1
ComboBox2.RemoveItem i
Next i
For i = ComboBox3.ListCount - 1 To 0 Step -1
ComboBox3.RemoveItem i
Next i
'Put the dictionaries/lists as source of the Comboboxes
ComboBox1.List = dic1.keys
ComboBox2.List = dic2.keys
ComboBox3.List = dic3.keys
ComboBox4.List = dic4.keys
End Sub
Hope this works well for you- if so please mark this Answer as Selected.
