Can I set a range variable in Public section?

[bernie1961]

I've looked through some of the posts on working with arrays, but I can not figure out how to incorporate the suggestions into my Userform...

The form I've created is working, however it is quite a cumbersome code (I am happy it's working at least - it's my first attempt at VB.)

I have several comboBoxes which are populated from data located in a separate workbook - in addition there are several textBoxes which are auto-filled dependent on the value entered in some of these comboBoxes.

As I mentioned, the form does work however it is slow to load and also everytime a combobox is filled the workbook with the source data is opened and then closed.

I am hoping there is a way that I can preload my data ranges on initialization and can then have them available to use throughout my form.

I have tried different approaches in the Public portion but none of my attempts were allowed. I have attached the workbooks with the form and the source data, and below is a sample of the code that is repeated throughout the form. (There are 31 ComboBoxes using a range from the source workbook, however I believe they all will be calling from only 7 ranges of data, my code calls in the same range of cells for now just so I can see if everything is working.)


Code for getting comboBox lists:

Please Login or Register  to view this content.

Code to fill-in textBoxes (ComboBoxes 1 & 3 do not auto-fill textboxes):

Please Login or Register  to view this content.

I would appreciate any suggestions!
Thanks
Bernadette

[mikerickson]

Since ComboBox2 is populated from
C:\DATAENTRY\EXCEL_DATA_ENTRY_WORKSHEETS\LOOKUP_DATA\LOOKUP_FIELDS.xls] Worksheets(1)A5:A21
I would think that this will populate the textbox without having to open and close the workbook.

Please Login or Register  to view this content.

Similarly for combo box 4