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:

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer

Dim SourceWB As Workbook

     With Me.ComboBox1
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off,
        ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\DATAENTRY\EXCEL_DATA_ENTRY_WORKSHEETS\LOOKUP_DATA\LOOKUP_FIELDS.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("A5:A21").Value
        ' get the values you want

        Set SourceWB = Nothing
        Application.ScreenUpdating = True
        ListItems = Application.WorksheetFunction.Transpose(ListItems)
        ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
End With
    With Me.ComboBox2
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off,
        ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\DATAENTRY\EXCEL_DATA_ENTRY_WORKSHEETS\LOOKUP_DATA\LOOKUP_FIELDS.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("A5:A21").Value
        ' get the values you want

        Set SourceWB = Nothing
        Application.ScreenUpdating = True
        ListItems = Application.WorksheetFunction.Transpose(ListItems)
        ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
End With

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

Private Sub ComboBox2_Change()
Dim ws As Worksheet
Set ws = Worksheets("Data_Entry")
Set SourceWB = Workbooks.Open("C:\DATAENTRY\EXCEL_DATA_ENTRY_WORKSHEETS\LOOKUP_DATA\LOOKUP_FIELDS.xls", _
            False, True)
            
    With SourceWB.Worksheets(1)

 Me.TextBox4.Value = .Range("A5").Offset(Me.ComboBox2.ListIndex, 1)

SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
    End With
End Sub
Private Sub ComboBox4_Change()
Dim ws As Worksheet
Set ws = Worksheets("Data_Entry")
Set SourceWB = Workbooks.Open("C:\DATAENTRY\EXCEL_DATA_ENTRY_WORKSHEETS\LOOKUP_DATA\LOOKUP_FIELDS.xls", _
            False, True)
            
    With SourceWB.Worksheets(1)

 Me.TextBox6.Value = .Range("A5").Offset(Me.ComboBox4.ListIndex, 1)

SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
    End With
End Sub

I would appreciate any suggestions!
Thanks
Bernadette