Combo box in excel using vba code?

Question

This code creates dropdown with given values, but if i select a particular value entire list is regenerating in drop down, how to avoid that?

Sub ComboBox1_Change()
With Sheet1.ComboBox1
    .AddItem "0"
    .AddItem "5"
    .AddItem "10"
    .AddItem "25"
    .AddItem "30"
End With
End Sub

Is it on a UserForm? Populate the combobox with UserForm Initialize event instead. — Subodh Tiwari sktneer
– Subodh Tiwari sktneer, Commented Apr 27, 2017 at 10:14
Is this in Private Sub Workbook_Open() ? I set up my combo box data here so it is available when the worksheet opens — MiguelH
– MiguelH, Commented Apr 27, 2017 at 10:15
You might also want to include a ` .Clear` after the With to clear any previous content. Also use .ListIndex = 0 before the End With to set the initial index value (i.e. show a blank) — MiguelH
– MiguelH, Commented Apr 27, 2017 at 10:18

Ralph · Accepted Answer · 2017-04-27 15:49:04Z

1

As pointed out in the comments above this is a rather odd way of initializing the ComboBox because this code runs each time you are changing the value of the ComboBox. It would be better to run this code only once (when the Excel file is being opened or when you activate that sheet or with some other event).

Yet, if you want to stick with the above approach then I suggest the following solution:

Option Explicit

Sub ComboBox1_Change()

Dim tmp As String
With Sheet1.ComboBox1
    tmp = .Value
    .Clear
    .AddItem "0"
    .AddItem "5"
    .AddItem "10"
    .AddItem "25"
    .AddItem "30"
    .Value = tmp
End With

End Sub

edited Apr 27, 2017 at 15:49

answered Apr 27, 2017 at 10:25

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shai Rado Over a year ago

@Ralph gave you +1, even though I miss the video ;)

Collectives™ on Stack Overflow

Combo box in excel using vba code?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related