View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Hank Scorpio Hank Scorpio is offline
external usenet poster
 
Posts: 103
Default 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! *