Worksheet Combo Box
 

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

Worksheet Combo Box
 

On Thu, 17 Jun 2004 04:08:01 -0700, "Stan"
wrote:

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.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Worksheet Combo Box
 

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

Stan

"Hank Scorpio" wrote:

On Thu, 17 Jun 2004 04:08:01 -0700, "Stan"
wrote:

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.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Worksheet Combo Box
 

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

"Stan" wrote in message
...
Hank, that's spot on!
Thanks very much!

Stan

"Hank Scorpio" wrote:

On Thu, 17 Jun 2004 04:08:01 -0700, "Stan"
wrote:

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.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *