Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract External Data according to a list of references | Excel Discussion (Misc queries) | |||
Sorting Data with External References | Excel Discussion (Misc queries) | |||
External data references in EXCEL 2002 | Excel Discussion (Misc queries) | |||
Cell References and External Data | Excel Worksheet Functions | |||
External References | Excel Discussion (Misc queries) |