Excel VBA ComboBox Default Value

user_6603488 0
Viewed 34155

Am looking for a way to have the combobox on my userform to have a default value of nothing or something like "Choose From Below".

Have tried various google suggestions but the value that shows in my combobox is blank if it's the first iteration of the code, or worse, the previously chosen value.

Code is below...

' Begin Code
Private Sub UserForm_Initialize()
    Dim RngTags As Range, RngNames As Range, i As Long

ComboBox1.Value = "SomeText"

'Set rng1 = Sheets("Admin").Range("deptrange2")
Set rng1 = Range("ALLDEPT")

With ComboBox1
    .ColumnCount = 1
    .Style = fmStyleDropDownList
    .TextAlign = fmTextAlignLeft
    .BoundColumn = 1

    For i = 1 To rng1.Count
        .AddItem rng1(i).Value
        .List(.ListCount - 1, 1) = rng1(i).Value
    Next i
End With

End Sub


' Puts the value chosen from the list on admin f6
Private Sub ComboBox1_Change()
    Sheets("Admin").Range("f8").Value = ComboBox1.Value

    Call myUnLoad
End Sub

' Gets rid of userform
Sub myUnLoad()
    UserForm1.Hide
End Sub
' End code

Thanks

Andrew

combobox default vba excel
2 Answers

Explanation follows Under code:

With ComboBox1
    .RowSource = ""
 .ColumnCount = 1
    .Style = fmStyleDropDownList
    .TextAlign = fmTextAlignLeft
    .BoundColumn = 1
         .AddItem ("Choose From Below")
    For i = 1 To rng1.Count
        .AddItem rng1(i).Value
        .List(.ListCount - 1, 1) = rng1(i).Value
    Next i
    .Value = .ItemData(0)
End With

You're looking for the ItemData() array property. It goes right under where you did all the AddItems. One needs to add an item that states "Choose From Below". It's always safe to clear the rowSourse otherwise it stores data that one might have entered previously. I think it makes the most sense to put the "Choose from below" item in the first index 'ItemData(0)'. One can do that by just making it the first item you added to the combobox. Then One can in all known cases reference it as .ItemData(0) The top answer is wrong the ListIndex property is read only. You can't set it to a certain value. Here is an error message I got when I tried to use it as the top comment states: Error One can only change other values based on the LinstIndex value. Example:

Dim TopAnswerIsWrong As String

If (Combobox1.ListIndex = 5) Then         'that means if the 6th item is chosen. 
    TopAnswerIsWrong = "How did it get 5 up votes?" ' remember the index of the first item is zero as the items are stored in an array
Else                                                 
    TopAnswerIsWrong = "I guess maybe you can use it that why what but for me it always gives errors" 
End If

For detailed, but sometimes complicated, resources you can use: https://docs.microsoft.com/en-us/office/vba/api/overview/

user_148732010
Related