Hide empty ComboBox list items

Question

I have:

3 columns with data
3 arrays
3 comboboxes

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

If one cell is empty, does that mean that all other cells in that row are empty as well? If not, what do you want to display to the user if he picks a value out of a row that contains empty cells? — Verzweifler
No, there are some rows where only one or two out of three cells contain data, and sometimes all three. If the user picks a value on a row containing empty cells, the "empty cell combobox" should be blank — Krusing
Are your values unique (per column)? If yes, you can keep the empty values in your arrays (thus preserving order and matching), and in ViewSelected you look up the selected value (in the array, not the Control!), note the index and match it in your corresponding arrays. — Verzweifler
Having duplicates and blanks leaves me at a loss for now. I'm thinking about a way to keep track of blanks per column via an extra counter, but don't have a reliable solution yet... — Verzweifler

Verzweifler Verzweifler · Accepted Answer · 2015-08-11T11:49:55

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)

Hide empty ComboBox list items

1 Answers