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]

This could work. It is also possible to set a default value in ComboBox5 (that's a property you can edit in the form).

If ComboBox5.Value = "SH" Or ComboBox5.Value = "CDF" Then
'your code here
Else
'do nothing? Maybe something else. Leave ComboBox1 to 4 empty?
End If

 

[KalilMe]

something like this?

Set ws = Sheets(ComboBox5.Value)
    If ComboBox5.Value = "SH" Or ComboBox5.Value = "CDF" Then
    Set ws = Sheets(ComboBox5.Value)
'your code here
Else
'do nothing? Maybe something else. Leave ComboBox1 to 4 empty?
Exit Sub
End If

in my opinion when combobox5 is empty when run form doesn't treat this problem!

 

[Rijnsent]

No, that will crash, you need to move the Set ws= inside the IF statement.

If ComboBox5.Value = "SH" Or ComboBox5.Value = "CDF" Then
    Set ws = Sheets(ComboBox5.Value)
    'your code here
Else
   'do nothing? Maybe something else. Leave ComboBox1 to 4 empty?
   Exit Sub
End If

 

[KalilMe]

ok
but will mismatch error in this line

ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))

the reason is from sheet in this line

a = Sheets("ws").Range("A1:G" & Sheets("ws").Range("G" & Rows.Count).End(3).Row).Value

how can I indicate to sheet based on selected from combobox5?

 

[Rijnsent]

Set ws = Sheets(ComboBox5.Value)
    If ComboBox5.Value = "SH" Or ComboBox5.Value = "CDF" Then
    Set ws = Sheets(ComboBox5.Value)
    'This:
    a = Sheets("ws").Range("A1:G" & Sheets("ws").Range("G" & Rows.Count).End(3).Row).Value
   'Can become:
    a = ws.Range("A1:G" & ws.Range("G" & Rows.Count).End(3).Row).Value
   'So after you set the object, you can use that object instead of having to type Sheet("sheetname_here") again.
End If

 

[KalilMe]

still mismatch error in the same line!

 

[dmt32]

Hi
I note that you have started a thread at another site here: subscript out of range when run user form to select sheet from combobox which I assume is a continuation of this thread?

I have not read all posts here so a bit of guess but see if this change to your code resolves your issue

Make a backup of your workbook & then delete UserForm_Activate code.

Place both codes in userform code page.

Private Sub ComboBox5_Change()
    Dim i           As Long
    Dim ws          As Worksheet
    Dim SheetName   As String
    Dim data        As Variant
    Dim dic(1 To 4) As Object, box(1 To 4) As Object
   
    SheetName = Me.ComboBox5.Value
    If Len(SheetName) = 0 Then Exit Sub
   
    For i = 1 To UBound(dic)
        Set dic(i) = CreateObject("Scripting.Dictionary")
        Set box(i) = Me.Controls("ComboBox" & i): box(i).Clear
    Next i
   
    If Evaluate("ISREF('" & SheetName & "'!A1)") Then
   
        Set ws = ThisWorkbook.Worksheets(SheetName)
        data = ws.Range("A1").Resize(ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, 7).Value
       
        For i = 2 To UBound(data, 1)
            If Len(data(i, 2)) > 0 Then dic(1)(data(i, 2)) = Empty
            If Len(data(i, 3)) > 0 Then dic(2)(data(i, 3)) = Empty
            If Len(data(i, 4)) > 0 Then dic(3)(data(i, 4)) = Empty
            If Len(data(i, 6)) > 0 Then dic(4)(data(i, 6)) = Empty
        Next i
       
        For i = 1 To UBound(dic)
            box(i).List = dic(i).keys
        Next i
          
    Else
       
        MsgBox SheetName & Chr(10) & "Worksheet Not Found", 48, "Error"
       
    End If
   
End Sub

Private Sub UserForm_Initialize()
  
    With Me.ComboBox5
        .List = Array("SH", "REPORT", "FINAL")
        .Style = fmStyleDropDownList
        .ListIndex = 0
    End With
   
    With Me.ListBox1
        .RowSource = ""
        .ColumnWidths = "100;100;100;130;80,80,80"
        .ColumnCount = 7
        .Font.Size = 10
    End With
   
End Sub

Solution is not fully tested but hopefully, goes in right direction for you

Dave

 

[KalilMe]

so I suppose to cancel in UserForm_Activate() but will show permission denied in this line

 .List = Array("SH", "CDF")

 

[dmt32]

so I suppose to cancel in UserForm_Activate() but will show permission denied in this line

 .List = Array("SH", "CDF")

UserForm_Initialize replaces UserForm_Activate
and populates ComboBox5 based on values in array("SH","REPORT","FINAL") which you stated in your first post.

Dave

 

[KalilMe]

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