Loading Worksheet Range to VBA Array

HI!

I’m trying to populate a combobox control using the following code:

Please Login or Register  to view this content.

As the list of names in Sheet1 gets longer, n increases (using the COUNTA function), so when the user form initializes, ComboBox1 get filled with the full list of names. This works fine, BUT I get an “Application-defined or object-defined error” if Sheet1 is not active when the code is running. I could use Activate method, but the problem is that I want to hide (visible=false) the worksheets from the end-user and then I get the same error again!

Any ideas how to work around this??

THANKS!!

Hello JohanF,

Provided everything is on "Sheet1", this code will find the last row in column "A", and load the combo box "ComboBox1". Change the variables you need to match what you are doing.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thanks Leith!

I like your approach from a slightly different angle than the one from where I started. I can follow and understand it.

Here's another suggestion I got from another forum:

Please Login or Register  to view this content.

Thanks again!

Johan