Listbox Propery code

[Mike Fogleman]

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.CurrentRegion, 2)
Sheets("CLI Parameters").Range("H6").Value =
Application.WorksheetFunction.VLookup(Sheets("CLI Parameters").Range("E1"),
Workbooks("System.tbl").Sheets("System").Cells.CurrentRegion, 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

 

[Tom Ogilvy]

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

 

[Mike Fogleman]

Thanks Tom, but I still get the error Can not set the ControlSource
property.

 

[Mike Fogleman]

I got it to work with this finally. I had to activate each WB in turn,
before setting the properties.

System.tbl is active when UserForm Loads:

Private Sub UserForm_Initialize()
RowCount = Selection.Rows.Count
SysList.RowSource = Workbooks("System.tbl").Worksheets("System"). _
Range("A2:A" & RowCount).Address(External:=True)
Workbooks(wbname).Activate
SysList.ControlSource = Workbooks(wbname).Worksheets("CLI Parameters"). _
Range("E1").Address(External:=True)
End Sub