Listbox Propery code
Thanks Tom, but I still get the error Can not set the ControlSource
property.
"Tom Ogilvy" wrote in message
...
Private Sub UserForm_Initialize()
RowCount = Selection.Rows.Count
SysList.RowSource = workbooks("System.tbl").worksheets("System"). _
Range("A1").CurrentRegion.Address(External:=True)
SysList.ControlSource = Workbooks(wbname).Worksheets("CLI Parameters"). _
Range("E1").Address(External:=True)
End Sub
--
Regards,
Tom Ogilvy
Mike Fogleman wrote in message
...
I have a listbox("SysList") on a UserForm("SystemForm") that I want to
code
the RowSource on a different workbook and the ControlSource in the same
WB
as the UserForm resides. The name of the other WB is constant because it
is
really a database table, but the WB where the UserForm resides will
change
when the user runs the macros and SaveAs "Whatever,xls". The other WB is
open and active at the time of Load UserForm, so the RowSource populates
correctly, but the RowSource just will not happen for me. Does anyone
see
my
problem in the following code:
Public wbname, wb2name
Option Explicit
Dim fname As Variant
Dim fileToOpen
Sub Get_Plant_Miles()
wbname = ActiveWorkbook.Name 'The main WB
MsgBox "Click OK to this message and a file browser window will open." _
& Chr(10) & "Browse to the LES5 directory and select 'System.tbl'."
Application.ScreenUpdating = True
Sheets("CLI Parameters").Activate
Application.ScreenUpdating = False
fileToOpen = Application _
.GetOpenFilename("Table Files (*.tbl), *.tbl")
Workbooks.Open filename:=fileToOpen
Sheets("System").Select
Columns("A:A").Delete
ActiveCell.CurrentRegion.Select
Selection.sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
wb2name = ActiveWorkbook.Name 'The database system.tbl
Load SystemForm
SystemForm.Show
Workbooks(wbname).Activate
Sheets("CLI Parameters").Activate
Sheets("CLI Parameters").Range("C6").Value =
Application.WorksheetFunction.VLookup(Sheets("CLI
Parameters").Range("E1"),
Workbooks("System.tbl").Sheets("System").Cells.Cur rentRegion, 2)
Sheets("CLI Parameters").Range("H6").Value =
Application.WorksheetFunction.VLookup(Sheets("CLI
Parameters").Range("E1"),
Workbooks("System.tbl").Sheets("System").Cells.Cur rentRegion, 3)
Workbooks("System.tbl").Close
End Sub
The UserForm Code is:
Dim RowCount As Integer
Private Sub CommandButton1_Click()
SystemForm.Hide
Unload SystemForm
End Sub
Private Sub UserForm_Initialize()
RowCount = Selection.Rows.Count
SysList.RowSource = "System.tbl!A2:A" & RowCount
SysList.ControlSource = wbname & "CLI Parameters!E1"
End Sub
TIA, MIke
|