4

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.

Toddleson
  • 4,321
  • 1
  • 6
  • 26
  • 1
    Seems it wants that method in the `GotFocus` event, unfortunately that isn't available to form controls. Seems the solution is to use `SendKeys` unfortunately. https://stackoverflow.com/questions/13040694/excel-display-combobox-dropdown-by-vba – Warcupine Apr 27 '22 at 15:11
  • @Warcupine Very solid workaround. The answer on that linked question will be my implemented solution. I'm not sure if this now qualifies my question as a duplicate. I'm okay with closing the question or giving you the checkmark. – Toddleson Apr 27 '22 at 15:17
  • 1
    Think its a dupe, I don't really have anything to add to Siddharth's answer, and if folks google the error this post should work as a nice signpost. – Warcupine Apr 27 '22 at 15:18
  • Late, but possibly somehow clarifying comment fyi: see [unexpected behaviour after using setfocus](https://stackoverflow.com/questions/55011102/vba-userform-unexpected-behaviour-after-using-setfocus-on-a-textbox/55030162#55030162) and background infos at [validation message of .. interrupts ..](https://stackoverflow.com/questions/53392937/validation-message-of-text-box-entry-on-modeless-form-interrupts-text-selection/53496763#53496763) @Toddleson – T.M. Sep 17 '22 at 18:39

0 Answers0