Combo Box in VBA Excel

Question

I have a Combo box on sheet1 of a excel workbook. I add some items to it during a process. Once I close the workbook all the items of Combo box are getting cleared. How to avoid this?

    Public Sub ComboBox1_Change() 
    Dim ws As Worksheet 
    Dim match As range 
    Me.ComboBox1.AddItem range("C3").Value 
    End Sub

Create a hidden sheet and store the values in that from the combobox before you close the workbook. When you open the workbook later, reload the combo from that. — Siddharth Rout
– Siddharth Rout, Commented Jan 28, 2014 at 6:47

Amadea · Accepted Answer · 2014-01-28 06:48:12Z

2

Add items to a range in your worksheet, somewhere hidden, and define a name for that range. In the properties of the combobox under ListFillRange, write the name that you defined for that range.

answered Jan 28, 2014 at 6:48

Amadea

511 gold badge1 silver badge10 bronze badges

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

2 Comments

Siddharth Rout Over a year ago

+ 1 Good idea but in this case you need to mention that the user has to add the items to the named range instead of combobox at runtime ComboBox1_Change() else you will get a permission denied error.

Amadea Over a year ago

Right. Plus, didn't see your comment up there. :)

Collectives™ on Stack Overflow

Combo Box in VBA Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related