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 :)

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

--
Message posted from http://www.ExcelForum.com

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! :)
-----Original Message-----
The combobox has to know somehow that it has those list
items, so maybe
you could place the pice of code in the workbook's open
event, or
another way is to have the elements in a range and link
this range to
the combo-box

- Mangesh


---
Message posted from http://www.ExcelForum.com/

.

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

--
Message posted from http://www.ExcelForum.com

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! :D


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

Sub Auto_Open()

This would be enough.

- Mangesh


---
Message posted from http://www.ExcelForum.com/

.

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 he

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.

--
Regards,
Tom Ogilvy

"sarah" wrote in message
...
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! :)
-----Original Message-----
The combobox has to know somehow that it has those list
items, so maybe
you could place the pice of code in the workbook's open
event, or
another way is to have the elements in a range and link
this range to
the combo-box

- Mangesh


---
Message posted from http://www.ExcelForum.com/

.

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!

-----Original Message-----
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 he

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.

--
Regards,
Tom Ogilvy

"sarah" wrote in message
...
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! :)
-----Original Message-----
The combobox has to know somehow that it has those list
items, so maybe
you could place the pice of code in the workbook's open
event, or
another way is to have the elements in a range and link
this range to
the combo-box

- Mangesh


---
Message posted from http://www.ExcelForum.com/

.



.

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!

-----Original Message-----
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!

-----Original Message-----
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 he

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.

--
Regards,
Tom Ogilvy

"sarah" wrote in
message
...
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! :)
-----Original Message-----
The combobox has to know somehow that it has those
list
items, so maybe
you could place the pice of code in the workbook's
open
event, or
another way is to have the elements in a range and
link
this range to
the combo-box

- Mangesh


---
Message posted from http://www.ExcelForum.com/

.



.

.