ActiveX Combo Box on Excel sheet 1

[sjh]

Hi, this is a beginner question.
After I create a Combo box (control, not forms) on a excel sheet, where do I define the values for the combo box list? I looked at the properties, but I couldn't find the correct place for it. I want the values to come from another excel sheet (in the same workbook).

Thank you.

 

[SkipVought]

Hi,

Try ListFillRange

You can use a range on a sheet

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sjh]

Thank you Skip for keep helping me out! ListFillRange worked like a charm.

Do you also know how to just enter the values for the combo box list - and not refer to the range on a sheet?

 

[SkipVought]

ComboBox1.AddItem "Apple"
ComboBox1.AddItem "Pear"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sjh]

Where should I put the .AddItem code? Should it be under Worksheet.open event or something?

Thanks again. Sorry about keep coming up with new questions...

 

[SkipVought]

Sure

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

You will also want to clear the box if it was saved previously...

Private Sub Workbook_Open()
  With ComboBox1
    .Clear
    .AddItem "Apples"
    .AddItem "Pears"
...
  End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sjh]

Hi,
For initializing variables in a worksheet, what event is more appropriate? I tried using Worksheet_Open method and wrote code for adding items to the combo box, but somehow this method doesn't seem to get called.

Thank you,

 

[marklenel]

That's odd, it should do.

Try inserting this line into your Workbook_Open() event as a quick visual check. Save & reopen the file.

MsgBox ("Workbook_Open is running!&quot

Also, do you have Macro Security enabled? (Tools... Macros... Security...)

 

[SkipVought]

There is NO...

Worksheet_Open event

There IS a...

Worksheet_Activate event and
Workbook_Open event.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com