Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

how populate data in listbox by selected sheets

0

Hello

in bold lines I try to  populate data on form  when select sheet name from combobox5 , but I'm not sure how do that correctly. shows subscript out of range in this line

 Set ws = Sheets(ComboBox5.Value)

should populate data on form for each sheet alone  when select sheet name from combobox5.

Dim a As Variant
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
    Set ws = Sheets(ComboBox5.Value)
    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
    '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


Answer
Discuss

Answers

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

Discuss

Discussion

thanks
but if I select CDF sheet will show data for SH sheet !
I suppose the data show based sheet name in combobox5.
Kalil (rep: 42) Nov 28, '24 at 12:11 pm
Kalil. I didn't check that since it wasn't in your question (and I can't now) but I suspect the problem is in your FilterData code called by the CB5_Change  procedure (not the corrected UF_Activate code in my Answer). Please check snd let me know.
John_Ru (rep: 6537) Nov 28, '24 at 12:35 pm
You could also try changing to this... 
Private Sub ComboBox5_Change()
    If ComboBox5.Value <> "" Then Call UserForm_Activate
End Sub
 
John_Ru (rep: 6537) Nov 28, '24 at 12:41 pm
it wasn't in your question 
this is waht I said
should populate data on form for each sheet alone  when select sheet name from combobox5.
mean every sheet contains data individually  when selection from CB5 .
but I suspect the problem is in your FilterData code called by the CB5_Change  procedure
but I suppose when change  to this
a = ws.Range("A1:G" & ws.Range("G" & Rows.Count).End(3).Row).Value

the FilterData code called  should relate with variable a in UserForm_Activate()
I don't think  the problem from FilterData  as long  depends on variable a in UserForm_Activate().
Kalil (rep: 42) Nov 28, '24 at 12:54 pm
Kalil. Please see Revision #1 29 November 2024 to my Answer (plus second attached file)   
John_Ru (rep: 6537) Nov 29, '24 at 9:17 am
thanks very much.
Kalil (rep: 42) Nov 29, '24 at 10:21 am
Thanks for selecting my Answer, Kalil. 
John_Ru (rep: 6537) Nov 29, '24 at 4:20 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login