two request on userform delete empty in combobox and implementation sheets

[KalilMe]

Hello ,
I've found code for Dante Amore and I modified based on my requirements but I face some problems !
first when populate items in combobox1,2,3,4 will show empty among items because there are empty cells among cells in sheets . so how delete any empty in combobox1,2,3,4?
second I would change implementation from one sheet to multiple sheet by put specific sheets in array("SH","REPORT","FINAL")
the original code just deal with SH sheet how make it to deal with specific sheets?

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
 
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic3 = CreateObject("Scripting.Dictionary")
  Set dic4 = CreateObject("Scripting.Dictionary")
  a = Sheets("SH").Range("A1:G" & Sheets("SH").Range("G" & Rows.Count).End(3).Row).Value
  With ListBox1
    .RowSource = ""
    .ColumnWidths = "100;100;100;130;80,80,80"
    .ColumnCount = 7
    .Font.Size = 10
  End With

  For i = 2 To UBound(a, 1)
    dic1(a(i, 2)) = Empty
    dic2(a(i, 3)) = Empty
    dic3(a(i, 4)) = Empty
    dic4(a(i, 6)) = Empty
  Next
 
  ComboBox1.List = dic1.keys
  ComboBox2.List = dic2.keys
  ComboBox3.List = dic3.keys
  ComboBox4.List = dic4.keys

End Sub

I hope finding help to fixing the code.
thanks.

 

[Rijnsent]

Hi KalliMe,
I've added some comments in your code to make it more readable. So basically you'd need to do an extra IF statement in the For...Next loop. The If a(i, 2) <> "" Then dic1(a(i, 2)) = Empty should check if the value is not empty (if there are numbers in that list, it could be that you'd need to use e.g. a(i,2)>0 to check that the value is not empty.
And the code currently only loads the range of Sheets("SH"), but you want to add the other ones of the other sheets to the same list?
Cheers,
Koen

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
    
    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("SH").Range("A1:G" & Sheets("SH").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

 

[KalilMe]

thanks

And the code currently only loads the range of Sheets("SH"), but you want to add the other ones of the other sheets to the same list?

yes I would that,pleas.

 

[KalilMe]

Sorry I forgot that !
the combobox5 will show sheets .
so should be like this

'
'
Dim WS As Worksheet
    Set WS = Sheets(ComboBox5.Value)

    '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
'
'

but will show subscript out of range in

Set WS = Sheets(ComboBox5.Value)

any help for fixing ,please?

 

[Rijnsent]

What value does ComboBox5.Value have? To find out, add a

Debug.Print ComboBox5.Value

above that Set WS= line. Alternatively, you could set a breakpoint on that Set WS= line and check it out.

Normally, when building macros/code/VBA, you'd add some Debug.Print so you can check what's happening when your code runs all at once. That is the case with forms, but you can use breakpoints for that. For macros outside forms: use F8 to start the code, pressing F8 again will just do one step at a time. If you want to learn more about how to debug code: Debugging Macros , and for more on VBA, check out this tutorial: Excel VBA Programming - a free course for complete beginners (free and has good basics).

 

[KalilMe]

What value does ComboBox5.Value have? To find out, add a

just sheets names SH,CDF
the problem shows when run the form the combobox5 is empty . so I search for way to avoid this problem.
do you have any idea?

 

[Rijnsent]

Without the rest of your code and the form, that's hard to say. What you could do: add a line "Set c = ComboBox5" , put a breakpoint on that line, press F8 when that line is hit and check in the "local variables" window the values of ComboBox5.

 

[KalilMe]

here is the code

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

put a breakpoint on that line, press F8 when that line is hit and check in the "local variables" window the values of ComboBox5

will show empty for combobox5

 

[Rijnsent]

At the start, try this:

Dim i As Long
Dim ws As Worksheet
Set c = ComboBox5
'Or maybe
Set c = Me.ComboBox5
Set ws = Sheets(ComboBox5.Value)

If you put the breakpoint at that line and make a step with F8, it might give you a clue what's going wrong. It might be that that element is not called ComboBox5 in your form, but I can't see that from here.

 

[KalilMe]

for what C variable? , you don't declare !

f you put the breakpoint at that line and make a step with F8, it might give you a clue what's going wrong. It might be that that element is not called ComboBox5 in your form, but I can't see that from here.

but I answered you in post#6
the problem when run the form will show error because when run the form combobox5 will be empty.