A simple way to add a combo box and a few options in VBA without a Userform?

Question

EDIT: I'm using the ActiveX combo box.

I have been trying for a while to use different solutions that I've found here here and here but none of the examples seem to work to me. They don't explicitly make it clear where exactly to put

With Sheet1.ComboBox1
    .AddItem "Paris"
    .AddItem "New York"
    .AddItem "London"
End With

and anywhere I try to put them, nothing gets added to my combo box.

Furthermore, many of these tutorials seem to be using Userforms. I'm not interested in having a userform, I simply want a combo box with a few options in my Excel spreadsheet, which depending on which value a user chooses in the combo box, a cell changes its value. All that I want to do is to write something like this:

Private Sub ComboBox1_Change()
    .AddItem "Yes"
    .AddItem "Average"
    .AddItem "No"
End Sub

And then something along the lines of:

if ComboBox1 = "Yes"
then Range("D2").Value = 1

Is your ComboBox on the worksheet an ActiveX Control? That will make a difference. — Zack E, Apr 02 '19 at 21:20
Wouldn't that be much simpler to implement with data validation? — Mathieu Guindon, Apr 02 '19 at 21:25
@MathieuGuindon, you're right, this is easily solved with data validation, but I'm trying to learn some more VBA in my free time so that's why I'm trying to figure this out. — WoeIs, Apr 02 '19 at 21:26
I personally use data validation in my combo box as @MathieuGuindon suggested, since its a heck of a lot easier to maintain. — Zack E, Apr 02 '19 at 21:28
If you are using the Workbook_Open Event (as in your second link) to populate the Combo Box, then macros must be enabled for it to actually do anything. Make the document trusted to have this happen automatically. — Stax, Apr 02 '19 at 21:28
@Stax I already have a bunch of other VBA codes running in my file, so that is all set. — WoeIs, Apr 02 '19 at 21:29

Zack E · Answer 1 · 2019-04-02T21:33:44.150

1

If you want the ComboBox to load when the worksheet activates use this in the Worksheet Object in VBA: (this may cause the ComboBox to load dupicate entries when switching back and forth between sheets.)

Private Sub Worksheet_Activate()
    With ComboBox1
        .AddItem "Paris"
    End With
End Sub

Or you can place it in a standard module and call it when ever you need to: (this is my preferred method except I would use data validation, since its easier to maintain.)

Sub LoadCombo()
    With Sheet1.ComboBox1
        .AddItem "Paris"
    End With

End Sub

edited Apr 02 '19 at 21:33

answered Apr 02 '19 at 21:26

Zack E

696
7
23

Better load the content *once*. Doing this in `Worksheet.Activate` will re-populate the items every time the sheet is activated, and without first clearing the list, quickly shifting between Sheet1 and Sheet2 a few times will result in as many duplicate entries in the dropdown. Best place for this (IMO) is some `LoadCombo` method like you've got, invoked from the `Workbook_Open` handler. – Mathieu Guindon Apr 02 '19 at 21:28
I completely agree @MathieuGuindon. I was just showing the OP a couple of options since they wanted to learn a little more, and that way they can play around with it to see the different behavior. I will edit my answer to show that information as well. – Zack E Apr 02 '19 at 21:31
I can't quite get this to work. I've tried to add your first part in both my Sheet1 page and the ThisWorkbook page in the VBA editor, but it doesn't seem to add "Paris" to the object. – WoeIs Apr 02 '19 at 21:32
@WoeIs if you double-click the combobox on the actual worksheet, does it bring up the VBE in the worksheet's code-behind, inside a `_Change` handler for it? If yes, the identifier before the underscore is the name of the combobox you need to use to refer to it. If not, ...then you're not using an ActiveX control and the way to go about it is ...different. – Mathieu Guindon Apr 02 '19 at 21:35
create it in a standard module, make sure that you qualify the Sheet before the ComboBox, then call the Sub in the Workbook.Open event. See if that works. – Zack E Apr 02 '19 at 21:35
@MathieuGuindon, yes, when I double click on the combo box, it takes me to the "ComboBox1_Change()" handler. It seems like I'm using the correct name. Would a screenshot help? – WoeIs Apr 02 '19 at 21:41
@ZackE, I tried creating a module and put in the code there, but that also did not seem to work. What do you mean by qualifying the sheet? – WoeIs Apr 02 '19 at 21:43
try this `ThisWorkbook.Sheet1.ComboBox1` after the `With`. Provided that the codename of the Sheet is actually Sheet1 – Zack E Apr 02 '19 at 21:45
@WoeIs. Quick question: You arent trying to load the `ComboBox` with data on the `ComboBox_Change` event are you? I ask because of one of the examples in your question. – Zack E Apr 02 '19 at 21:57
@ZackE, this is what I currently have in my handles and where I have put them in the VBE: https://i.imgur.com/v9L2AQn.jpg and https://i.imgur.com/B4o4ISs.jpg – WoeIs Apr 02 '19 at 22:05
I would move loading the data into the ComboBox into a standard module then call the sub on the `Worksheet_Activate` event and see if that works. Im not sure why it isnt loading as i cannot duplicate that issue. – Zack E Apr 02 '19 at 22:37

A simple way to add a combo box and a few options in VBA without a Userform?

1 Answers1