External data references in a userform control

[Guest]

If I enter an external Excel file cell ref in the Rowsource property of a Listbox it works fine, however if I use the same format to change the Rowsource property I get an error in the VB statement (it goes red).

Can anyone tell me the correct format for
a) addressing a file in the same folder
b) addressing a file in a different folder
c) addressing a file on another drive.

Mike Rea

 

[Nigel]

To set a source row from the current activeworkbook use .....

UserForm1.ListBox1.RowSource = "sheet1!a1:a7"

To set it from a closed workbook, you need to open it first get the
rowsource data and close it. I use the following code. Here you can
specify the source drive, folders (directories), source data range etc as
required.
ListData is an array set up to store the rowsource items for loading into
your form control.

Private Sub UserForm_Initialize()
Dim ListData As Variant, i As Integer
Dim SourceWB As Workbook
With Me.ListBox1
.Clear
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("C:\filepath\filename.xls", False,
True)
ListItems = SourceWB.Worksheets(1).Range("A1:A3").Value
SourceWB.Close False
Set SourceWB = Nothing
Application.ScreenUpdating = True
ListData = Application.WorksheetFunction.Transpose(ListItems)
For i = 1 To UBound(ListData)
.AddItem ListData(i)
Next i
.ListIndex = -1
End With
End Sub


Cheers
Nigel

If I enter an external Excel file cell ref in the Rowsource property of a

Listbox it works fine, however if I use the same format to change the
Rowsource property I get an error in the VB statement (it goes red).

 

[Guest]

Thanks Nigel. I didn't realise that the external data source had to be open.
I did find an alternative though a bit messy. If I placed the external data cell reference in a spreadsheet cell in the active workbook then addressing that cell connected me to that data.