CODEWITHSUNDEEP
vbaexcel
nick
nick

Reputation: 149

Combo box in excel using vba code?

enter image description here

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

Upvotes: 1

Views: 586

Answers (1)

Ralph
Ralph

Reputation: 9444

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

enter image description here

Upvotes: 1

Related Questions