Run-Time Error '-2147417848 (80010108)' when using ComboBox events

Viewed 38

I am trying to create a Userform that has several ComboBoxes to aid the user in selecting data from large lists. I added an event so that after the user has selected a valid Customer in the Company ComboBox, the Division ComboBox is automatically given Focus and has the Dropdown opened.

This would be perfect if it didn't also cause the error:

Run-Time Error '-2147417848 (80010108)' 
Automation error
The object invoked as disconnected from its clients.

I know exactly which line is causing the error, because I removed basically every other line and the error appears when I try to add this line: Me.Division.DropDown (Division is the name of my 2nd ComboBox)

Here is the offending Event Script

Private Sub Company_Change()
    Me.Division.Clear
    Me.Division.Enabled = False
    
    If ValueExistsInList(Me.Company.List, Me.Company.Value) Then
        Me.Division.Enabled = True
        Me.Division.List = Array("A","B","C")
        Me.Division.SetFocus
        Me.Division.DropDown
    End If
    
End Sub

ValueExistsInList returns a Boolean, which is True when the user has entered a valid selection into the Company ComboBox. Then I insert the list of divisions for that selected company into Division.List. Then I try to do SetFocus and DropDown. The error doesn't happen on that line though, it actually waits until I get to End Sub and then the error appears. But if I remove the DropDown line, the error disappears.

Does anyone know how I can avoid this error while still being able to use DropDown?


I was able to create a reproducible example in a new workbook:

Step 1. Create a macro in a worksheet code module:

Sub Example()
    UserForm1.Show
End Sub

Step 2. Create a userform with 2 comboboxes, ComboBoxA and ComboBoxB

Step 3. Put this code module in the userform

Private Sub ComboBoxA_Change()
    Me.ComboBoxB.Enabled = False

    If ComboBoxA.Value = "A" Or ComboBoxA.Value = "B" Or ComboBoxA.Value = "C" Then
        Me.ComboBoxB.Enabled = True
        Me.ComboBoxB.Clear
        Me.ComboBoxB.Value = ""
        Me.ComboBoxB.List = Array("1" & ComboBoxA.Value, "2" & ComboBoxA.Value)
        Me.ComboBoxB.SetFocus
        Me.ComboBoxB.DropDown
    End If
End Sub

Private Sub UserForm_Initialize()
    Me.ComboBoxB.Enabled = False
    Me.ComboBoxA.List = Array("A", "B", "C")
    Me.ComboBoxA.SetFocus
End Sub

Step 4. Run the worksheet macro to open the userform.

Step 5. Select any option in ComboBoxA.

The error should appear.

0 Answers
Related