CODEWITHSUNDEEP
vbaexcelcombobox
Krusing
Krusing

Reputation: 307

Hide empty ComboBox list items

I have:

The idea is to select one combobox item and have the other two comboboxes show what data is in the other cells on the same row. I have more than 5000 rows of data, and well over a hundred empty cells in all three columns. There are some rows where only one or two out of three cells contain data. If I don't include empty cells, I run into trouble with wrong data being shown in the comboboxes, for instance if Bar(3,5,10) is empty, ComboBox1 item Foo(11) would not equal ComboBox2 item Bar(11), instead it shows ComboBox2 item Bar(8).

I need a way to hide the empty combobox items from the droplist but not removing them from the List.

The code as a sample

Form:

Private Sub UserForm_Initialize()

    Set FooBar = ActiveWorkbook.Sheets("foobar")

    Call PopulateArray(FooBar, 1, Foo)
    Call PopulateControl(Foo, UserForm1.ComboBox1)

    Call PopulateArray(FooBar, 2, Bar)
    Call PopulateControl(Bar, UserForm1.ComboBox2)

    Call PopulateArray(FooBar, 2, Baz)
    Call PopulateControl(Baz, UserForm1.ComboBox3)

End Sub

Private Sub ComboBox_Change()

    ViewSelected UserForm1.ActiveControl

End Sub

Module:

Public Foo(), _
       Bar(), _
       Baz()    As Variant

Public FooBar   As Worksheet


Function PopulateArray(Source As Worksheet, Columns as Integer, Target as Variant)

    With FooBar
        For i = 0 To .UsedRange.Rows.Count - 1
            ReDim Preserve Target(i): Target(i) = Cells(i + 1, Column)
        Next i
    End With

End Function


Function PopulateControl(Source As Variant, Target As Control)

    For i = 0 To UBound(Source)
        Target.AddItem Source(i)
    Next i

End Function


Function ViewSelected(Selected As control)

    Dim i As Integer: i = Selected.ListIndex
    For Each control in UserForm1.Controls
        If TypeName(control) = "ComboBox" Then control.ListIndex = i
    Next control

End Function

Example of the data

Column 1    Column 2    Column 3

Foo1        Bar1        Baz1
Foo1        Bar1        Baz2
Foo1        Bar2        Baz3
Foo1        Bar2        Baz4
Foo2        Bar11       Baz11
Foo2                    Baz12    'missing Bar11
Foo2        Bar12       Baz13
Foo2        Bar12                'missing Baz14

Upvotes: 1

Views: 1680

Answers (1)

Verzweifler
Verzweifler

Reputation: 940

OK, I think I have figured something out. This solution assumes that your arrays still contain the empty values. It also assumes, that you can in some way match the selected Control to the corresponding array.

Given the index of one column (the selected value), you want to find out the corresponding index of the other columns respecting the number of blanks in the original column.

So we look for the original index in the (in this example) corresponding array Foo:

Dim i As Integer
Dim j as Integer

For i = 0 to Selected.ListIndex
    if Foo(i) = "" Then j = j + 1
    j = j + 1
Next i

We just counted the number of blanks up until the selected value. Now you know the original index (including blanks) of the column the user selected. Finally, we look up the corresponding value in each (other) Control, here originating from Bar and Baz:

UserForm1.ComboBox1.Text = Bar(j)
Userform1.ComboBox2.Text = Baz(j)

Upvotes: 1

Related Questions