Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Listbox Propery code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,092
Default Listbox Propery code
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Listbox Propery code
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,092
Default 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






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,092
Default Listbox Propery code
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


"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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
listbox B conditional of input in Listbox A Kim K Excel Discussion (Misc queries) 1 October 31st 06 08:27 PM
listbox.value not equal to listbox.list(listbox.listindex,0) ARB Excel Programming 0 October 22nd 03 12:46 AM
ListBox List Code? Abdul[_4_] Excel Programming 1 October 10th 03 01:28 AM
Is refreshing listbox rowsource in listbox click event possible? Jeremy Gollehon[_2_] Excel Programming 4 September 25th 03 06:45 PM
Sorting ListBox results or transposing ListBox values to other cells for sorting Rob[_8_] Excel Programming 1 July 9th 03 04:35 AM


All times are GMT +1. The time now is 07:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement