Control Toolbox ComboBox: How To?

[Agimcomas]

Hello,

I am hoping to find an explanation on how Excel/VBA works with ComboBoxes. So far I couldn't find a step by step introduction for beginners. From doing research on the Internet, I learned that we can manipulate ComboBoxes by Creating Private Sub routines inside different objects (Sheet1, Thisworkbook, etc.)

I would like to know, (1) In which objects should I write code for things like populating the ComboBoxes List, assigning a linked cell, etc., and (2) how do I need to name this routines (I saw that VBA automatically creates routines like Workbook_Open, ComboBox1_Change, etc.)

My ultimate goal is simply to create a ComboBox with a predetermined list of possible values, and a linked cell. I am using Office 2003.

Thanks a lot!

[6StringJazzer]

If you go into the VBA development window and hit F2, you will see a list of VBA Excel objects on the left. If you have a combobox defined in your code then you will see ComboBox in the list. If you click on it, then the window to the right will show all attributes, methods, and events available for the ComboBox.

To populate a combobox, use the Add method:

cb.Add "Here's an item to add"

The attribute LinkedCell specifies the linked cell

cb.LinkedCell = Sheet1.Range("A1")

For the events (such as Change, Click), use the form

Public Sub cb_Click()

where cb is the name of your combobox.

You can also link a range to the list with the ListFillRange attribute, if you have the list defined in a worksheet someplace.

Hopefully this gets you started. More specific questions will get more specific answers.

[6StringJazzer]

Oh, and put the code in the same module for the entity containing the combobox. For example, if it's in a form, put it in the form code. If it's in a worksheet, put it in the code for that sheet. That's not where is has to go but that's best practice.

[Agimcomas]

Thanks 6StringJazzer. Your post is quite helpful.

I still don't understand completely the role of "events". What exactly is _Click, _Change, etc.?

Thanks again.

[6StringJazzer]

Excel VBA has something that you can think of as a monitor process. When the user clicks on a combobox called MyCombo, the monitor says, "Hey, somebody clicked. I wonder if there's code for that. Let me see if I can find a Sub called MyCombo_Click. If I find it, I'm going to call it." You never have to call that Sub from your own code but the system will call it if that event occurs. You just have to define what you want to happen when somebody clicks it.

I highly recommend anything written by John Walkenbach on VBA. That is how I learned it 8 years ago, from one of his books.

[Agimcomas]

That is an excellet explanation. Thanks a lot, 6StringJazzer !