Excel VBA keep Listbox from workbook persistent after sourse is closed

Question

I have a listbox on a userform that I am populating from an external workbook. If I keep the source spreadsheet open the listbox dispays correctly. When I close the external workbook the listbox seems no longer displays correctly and if you scroll the listbox text is replaced with a bunch of non print characters.

I launch the user form and when the user selects a checkbox the userform is expended to show the additional fields.

Private Sub chkRepaceFromList_Click()
    Dim sFileLocation As String
    'When checkbox is checked, add replace from list option
    If chkRepaceFromList = True Then
      Me.Width = 400
      sFileLocation = "C:\My File.xlsx"
      'Populate listbox with items in replace from list spreadsheet
      Call UpdateReplaceList
    Else
      Me.Width = 160
    End If
End Sub

UpdateReplaceList Populates the listbox with items from "C:\My File.xlsx"

Sub UpdateReplaceList()
    Dim sFileLocation As String
    Dim wbList As Workbook
    sFileLocation = "C:\My File.xlsx"
    Set wbList = Workbooks.Open(sFileLocation)
    wbList.Worksheets("Sheet1").Range("A10").Select
    Range("A10", Selection.End(xlDown).Offset(0, 1)).Select ' Select all data
    With lstReplaceListListBox
        .ColumnCount = 2
        .ColumnWidths = "100;100"
        '.RowSource = wbList.Range("A1:C20").Address
        .RowSource = Selection.Address
        '.RowSource = vList
    End With
    wbList.Close
End Sub

I would prefer not to keep "C:\My File.xlsx" open any longer than needed and if I do not close it the chkRepaceFromList_Click() event gets triggered and It tries to open the file that is already open.

I am using the Sub UpdateReplaceList as I need to use the same code when some other buttons are pressed.

Any suggestions would be appreciated.

Beans Beans · Accepted Answer · 2015-02-20T04:34:07

Turn's out that if you declare an unallocated array.

Dim vList() As Variant

It becomes allocated when data is assigned

vList = Selection 'copy data from workbook to memory as an array

Then you can assign the array to the listbox.

With lstReplaceListListBox
        .ColumnCount = 2
        .ColumnWidths = "100;100"
        .list = vList 'Copy array to listbox
    End With

And this stays persistent in the listbox when the source workbook is closed.

Excel VBA keep Listbox from workbook persistent after sourse is closed

1 Answers