Run-time error '380' when changing combobox value (using userforms and VBA in Excel)

Question

This is a very simplified example to demonstrate my problem.
Create a userform with a combobox and commandbutton.
Set the style property of the combobox to "fmStyleDropDownList".
Add the code

Private Sub CommandButton1_Click()
ComboBox1.Value = "But then it errors sometimes if I change it here"
End Sub

Private Sub UserForm_Initialize()
ComboBox1.Value = "I can initialize to any value I choose"
End Sub

When I run the userform I can click the command button all day long without getting any errors.
But, when I click on the drop-down arrow of the combobox and the text in the box gets highlighted, then the next time I click the command button I get "Run-time error '380': Could not set the Value property. Invalid property value"

Using style="fmStyleDropDownCombo" is not an option I want to consider. I tried using ComboBox1.ListIndex = -1 to clear the selection but that did not work.

Any ideas on how I can reliably avoid this error?

Answer 1

When you use fmStyleDropDownList style, your ComboBox value must match one of the item in the list but because you did not add any item, your list is currently Null.

To test this, make another ComboBox in your Userform and put this in your Userform:

Private Sub CommandButton1_Click()
    Debug.Print "=== Click ==="
    Debug.Print "ComboBox1 Type: " & TypeName(ComboBox1.List)
    Debug.Print "ComboBox2 Type: " & TypeName(ComboBox2.List)
            
    ComboBox1.Value = "But then it errors sometimes if I change it here"
    
    On Error Resume Next
    ComboBox2.Value = "But then it errors sometimes if I change it here"
    If Err.Number <> 0 Then Debug.Print "ComboBox2 - Error"
    
    Debug.Print "============="
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "I can initialize to any value I choose"
    ComboBox1.AddItem "But then it errors sometimes if I change it here"
    
    ComboBox1.Value = "I can initialize to any value I choose"
    ComboBox2.Value = "I can initialize to any value I choose"
    
    Debug.Print "ComboBox1 Type: " & TypeName(ComboBox1.List)
    Debug.Print "ComboBox2 Type: " & TypeName(ComboBox2.List)
End Sub

When you run the Userform, you should see this in the immediate window:

ComboBox1 Type: Variant()
ComboBox2 Type: Null

Clicking the button (as many times as you like) before clicking ComboBox2 and you will notice that the output will still be the same. (and no error too because the List is still Null)

Now click ComboBox2 and click the button again, you will see that the type for ComboBox2.List has now changed from Null to Variant and will actually trigger the error.

===============

So, to avoid this, you need to populate the List first, below shows the AddItem method:

Private Sub CommandButton1_Click()
ComboBox1.Value = "But then it errors sometimes if I change it here"
End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem "I can initialize to any value I choose"
ComboBox1.AddItem "But then it errors sometimes if I change it here"

ComboBox1.Value = "I can initialize to any value I choose"
End Sub

As far as I know, the preferred way to set a ComboBox value with such style is to loop through the ComboBox List property, check its value and change the ListIndex once found:

Private Sub CommandButton1_Click()
    Dim i As Long
    For i = 0 To ComboBox1.ListCount - 1
        If ComboBox1.List(i) = "But then it errors sometimes if I change it here" Then
            ComboBox1.ListIndex = i
            Exit For
        End If
    Next i
End Sub