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.

 

[dmt32]

what's the problem if I change sheets names?!
shouldn't be problem.

change the names listed in the array.

Dave

 

[KalilMe]

change the names listed in the array.

this is what I did it !

 

[KalilMe]

ok I see my bad
it was in window properties have list in row source
but doesn't show data based on selected sheet.

 

[dmt32]

this is what I did it !

Assuming you have made no other changes to code & combobox not manually linked to RowSource, then not understanding the problem? Code should report if sheet cannot be found

You will have to share copy of your workbook with forum if cannot resolve.

Dave

 

[KalilMe]

anyway thanks got solution.