combo boxes

[Sarah]

Hi,

I created a few comboboxes in my ms excel 2000 spreadsheet
and filled them using the VBA commands like:

ComboBox3.List = Array("No", "Yes")

When I later re-open the spreadsheet, the comboboxes are
empty! I have to recompile the code to see the items in
the comboboxes.

Does anyone know why this is happening and how to fix it!?

Thanks

 

[mangesh_yadav]

The combobox has to know somehow that it has those list items, so mayb
you could place the pice of code in the workbook's open event, o
another way is to have the elements in a range and link this range t
the combo-box

- Manges

 

[sarah]

hi,

I'm not sure I understand what you mean?? Sorry I haven't
used excel much!

What's the workbook's open event??

Thanks!

 

[mangesh_yadav]

ok. In that case, do the following. As you said earlier, you have th
vba command ComboBox3.List = Array("No", "Yes"). Name the sub in whic
this line is as:

Sub Auto_Open()

This would be enough.

- Manges

 

[Guest]

Hey Mangesh,

So the VBA code I changed it to:

Sub Auto_Open()
'... more stuff here

ComboBox3.List = Array("No", "Yes")

'... more stuff here
End Sub

But when I reopen the spreadsheet, the comboboxes are
still empty?!

I entered in the data on another sheet and I used the
range (sheet2!a1:a2)... to hold the data. but that
combobox is not in the location I want on the spreadsheet,
its just a separate userform. :S

Help please!

 

[Tom Ogilvy]

Workbook_Open is a special procedure that executes when the workbook is
opened (unless the user disables macros or security settings disable
macros).

You can read about events here:

http://www.cpearson.com/excel/events.htm
Chip Pearson's page on events. These events were introduced in xl97 and are
the preferred events. Prior to xl97, there were events such as Auto_Open,
Auto_Close and so forth. These are still supported. These older style
events go in regular or standard modules, while the newer events go in the
special modules ThisWorkbook (for workbook level events) or the related
Sheet module if sheet level events.

As for comboboxes, if you populate them with code (using additem as an
example), then the populated list lasts only as long as the workbook is
open. When closed, the items in the list are lost and must be repopulated.
This is why it was suggested you do this in the workbook_open or the older
equivalent Auto_open events.

 

[Sarah]

Hey Tom,

Thanks for the info.

But I'm still not quite understanding how to do this? I
just rename the Sub to Auto_Open/Workbook_Open ?? cuz that
doesn't seem to be working :S

Sorry I'm new to all this!

 

[sarah]

Hey!

Nevermind I figured it out! Yaaay!... I had the code in
the 'Sheet1 module' and it should've been in the
ThisWorkbook module.... thanks!