RowSource problem with Combobox in Excel

[IMAO]

Hello all,

I have 20 comboboxes on a userform in excel. Each box are supposed to drop down to reveal a long lists of values (thousands, say).

When the userform initializes, the comboboxes are empty. Then a script is run to set the rowsource for each combobox and it works fine. However, after the userform is unloaded, the rowsource definitions are lost - ie, next time I show the userform, I would need to run the scrip to set the rowsource again. I could set the rowsource manually by typing in the address in the property box; but it wouldn't be dynamic.

Is there any way to set the rowsources programmatically and make them permanent?

Thanks.

Justin

 

[johnwm]

Run your VBA loading code from the WorkSheet.Activate event

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[IMAO]

It would work. But I wonder if there's a way for the rowsources to stay with the combobox even after the spreadsheet is closed - you know, same as if I put in the rowsours by hand directly ?

Thanks.

Justin

 

[johnwm]

Yep, sure is for a standard excel VBA ComboBox. In Properties set Autoload to True and type the appropriate range into ListFillRange property

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[IMAO]

John,

Thanks very much for your help. I'm a bit lost - Autoload and ListFillRange are properties of a listbox right? wondering how I can make a combobox workk the same way?

Thanks again

Justin

 

[johnwm]

If you're using the normal VBA ComboBox then they are also properties of the ComboBox (at least in Office XP). Must admit I can't remember if they have always been in the old versions

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk