Dependent Combobox Compile error

Question

Hello all,

Hopefully someone can help me out.  I am working on a workbook that contains several activeX comboboxes all dependent on each other.  When filling in the sheet from scratch they work great.  However, if I save and close it and then come later to finish completing it I get a compile error upon opening the workbook for each combobox that has an entry.  Below is a simplified example of the code I am using.

Private Sub ComboBox1_Change()

    TimeSheet.ComboBox2.Clear

    If Me.ComboBox1.Text = vbNullString Then Exit Sub

    Dim ListCell As Range

    ComboBox2.ListFillRange = ""

    For Each ListCell In Range(Me.ComboBox1.List(Me.ComboBox1.ListIndex))

        Me.ComboBox2.AddItem (ListCell.Value)

    Next ListCell

End Sub

Any help would be much appreciated.

Answer 1

Well, I cannot create your error, so until you upload and share a file we are at a standstill....

Answer 2

No dice there, 

still produces the same error.

Answer 3

Maybe look for off-list entries:

Private Sub ComboBox1_Change()

    If Me.ComboBox1.ListIndex = -1 Then Exit Sub

    TimeSheet.ComboBox2.Clear

    If Me.ComboBox1.Text = vbNullString Then Exit Sub

    Dim ListCell As Range

    ComboBox2.ListFillRange = ""

    For Each ListCell In Range(Me.ComboBox1.List(Me.ComboBox1.ListIndex))

        Me.ComboBox2.AddItem (ListCell.Value)

    Next ListCell

End Sub

Answer 4

Thanks for taking the time to help me out.

So, I narrowed it down to the actual setting in the combobox properties that does not allow the user to type in an entry that is not on the list.  Where the "Style" setting is 0-fmStyleDropDownList.  If I change it to fmStyleDropDownCombo there is no compile error.

Regrettably, there are many comboxes that are dependant on combobox2 so I definitely can't have it where a user can type in that field.  Would you happen to have any ideas on how I could approach it differently?

Answer 5

Your code worked fine for me no matter what I did. Could you upload a file that shows the issue, and post a link to the file?