CODEWITHSUNDEEP
excelvbacombobox
dhruva_04
dhruva_04

Reputation: 141

Populate Combobox(form control) using VBA on Excel 2013

I am new to VBA. I have been trying for hours now to execute a simple function of populating a combobox(form control) using VBA code. I have looked on a lot of websites(including this one), but none of the codes seem to work. I have been using this code. (I am putting this code in a module) 

Sub populateDropDown303()

With Worksheets("S1 Fuel Consumption").Shapes("Drop Down 303").ControlFormat

.AddItem "this"

.AddItem "that"

End With

End Sub

Hey I got the code working. But every time I select a value from the the combobox dropdown, it runs the code again and displays the duplicate values. How do I remove that ?

Upvotes: 0

Views: 3574

Answers (2)

L42
L42

Reputation: 19727

Try this:

Sub populateDropDown303()
    Dim ws As Worksheet: Set ws = Worksheets("S1 Fuel Consumption")
    With ws.Shapes("Drop Down 303").ControlFormat
        .RemoveAllItems '~~> This is what you lack I think
        .AddItem "This"
        .AddItem "That"
    End With
End Sub

I added a new variable ws of Worksheet type so Intellisense kicks in.
That way, it will be easier for you to see the available methods and properties for the object you're working on. HTH.

Upvotes: 2

SWa
SWa

Reputation: 4363

With Worksheets("S1 Fuel Consumption").Dropdowns("Drop Down 303")
    .AddItem "this"
    .AddItem "that"
End with

Upvotes: 0

Related Questions