External data references in a userform control
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 |
External data references in a userform control
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 "jmikerea" wrote in message ... 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 |
External data references in a userform control
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. "Nigel" wrote: 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 "jmikerea" wrote in message ... 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 |
All times are GMT +1. The time now is 10:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com