Worksheet Combo Box

[Guest]

Hi,

Ok, I've created a sheet using combo boxes through the forms toolbar, but I want these to be created through the control toolbox, thus giving me flexibility through VBA.

The question is:
How do I get the fields I want to show in the combo box to actually be displayed there so they can be selected?

I've looked through the Help file but this only tells me how to do it using a form. I want to keep the boxes on the worksheet. I understand the ComboBox1.Additem "" term, just not sure where to initialise it (if that's what i need to be doing) when keeping the combo boxes on the worksheet.

Thanks for your help in advance.

Stan

 

[Hank Scorpio]

Hi,

Ok, I've created a sheet using combo boxes through the forms toolbar, but I want these to be created through the control toolbox, thus giving me flexibility through VBA.

The question is:
How do I get the fields I want to show in the combo box to actually be displayed there so they can be selected?

I've looked through the Help file but this only tells me how to do it using a form. I want to keep the boxes on the worksheet. I understand the ComboBox1.Additem "" term, just not sure where to initialise it (if that's what i need to be doing) when keeping the combo boxes on the worksheet.

Thanks for your help in advance.

I'm not sure what trigger you're going to be using to initialise the
combo box, but let's say you use a command button on the same sheet.
This is one way:

Private Sub CommandButton1_Click()

'This is in the worksheet's code module.
'Me therefore refers to the worksheet.

With Me.ComboBox1
.AddItem "Apple"
.AddItem "Orange"
.AddItem "Lemon"

'This will set the initial value.
.Text = "Apple"
End With

End Sub

When you click on the drop down, you'll see the full list ot entries.

Don't forget that in design mode you can also right click on the Combo
Box, open the Properties dialog and set the ListFillRange property to
the name of a range; eg A9:A11 (without any quotes), or the name of
any named range in the book. If the combo boxes are intended to have
fixed contents, that may be easier than using AddItem code.

 

[Guest]

Hank, that's spot on!
Thanks very much!

Stan

I'm not sure what trigger you're going to be using to initialise the
combo box, but let's say you use a command button on the same sheet.
This is one way:

Private Sub CommandButton1_Click()

'This is in the worksheet's code module.
'Me therefore refers to the worksheet.

With Me.ComboBox1
.AddItem "Apple"
.AddItem "Orange"
.AddItem "Lemon"

'This will set the initial value.
.Text = "Apple"
End With

End Sub

When you click on the drop down, you'll see the full list ot entries.

Don't forget that in design mode you can also right click on the Combo
Box, open the Properties dialog and set the ListFillRange property to
the name of a range; eg A9:A11 (without any quotes), or the name of
any named range in the book. If the combo boxes are intended to have
fixed contents, that may be easier than using AddItem code.

 

[Tom Ogilvy]

If you do want to use additem, then you might want to populate the boxes in
the workbook_open event

Chip Pearson has a page
http://www.cpearson.com/excel/events.htm

that gives a basic description of events.

--
Regards,
Tom Ogilvy

Hank, that's spot on!
Thanks very much!

Stan

but I want these to be created through the control toolbox, thus giving me
flexibility through VBA.using a form. I want to keep the boxes on the worksheet. I understand the
ComboBox1.Additem "" term, just not sure where to initialise it (if that's
what i need to be doing) when keeping the combo boxes on the worksheet.