Populate a list box with all active workbooks except the current one.
 

Hi,

I'm not sure how to go about this one...

I want a listbox control in a userform launched from
spreadsheetone.xls to display the names of all the other
spreadsheets. i.e. the user can select any workbook in the open
workbooks collection, but spreadsheetone.xls won't be in that list
(because the form is part of an exporting routine to copy charts and
tables from spreadsheetone.xls to a new worksheet in whichever open
workbook the user selects).

How do I put the names of all open workbooks into a listbox?

Travis

Populate a list box with all active workbooks except the current one.
 

this is for listbox1 on a userform:

Private Sub UserForm_Activate()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name < ActiveSheet.Name Then
Me.ListBox1.AddItem ws.Name
End If
Next
End Sub

--


Gary

"travis" wrote in message
...
Hi,

I'm not sure how to go about this one...

I want a listbox control in a userform launched from
spreadsheetone.xls to display the names of all the other
spreadsheets. i.e. the user can select any workbook in the open
workbooks collection, but spreadsheetone.xls won't be in that list
(because the form is part of an exporting routine to copy charts and
tables from spreadsheetone.xls to a new worksheet in whichever open
workbook the user selects).

How do I put the names of all open workbooks into a listbox?

Travis

Populate a list box with all active workbooks except the current o
 

How about something like this. When the userform is activated it populates
the list box...

Private Sub UserForm_Activate()
Dim wbk As Workbook

ListBox1.Clear 'Change the list box name as necessary
For Each wbk In Workbooks
If wbk.Name < ThisWorkbook.Name Then _
ListBox1.AddItem wbk.Name
Next wbk
End Sub
--
HTH...

Jim Thomlinson


"travis" wrote:

Hi,

I'm not sure how to go about this one...

I want a listbox control in a userform launched from
spreadsheetone.xls to display the names of all the other
spreadsheets. i.e. the user can select any workbook in the open
workbooks collection, but spreadsheetone.xls won't be in that list
(because the form is part of an exporting routine to copy charts and
tables from spreadsheetone.xls to a new worksheet in whichever open
workbook the user selects).

How do I put the names of all open workbooks into a listbox?

Travis

Populate a list box with all active workbooks except the curre
 

I was under the impression that Travis wanted the workbooks and not the
worksheets...
--
HTH...

Jim Thomlinson


"Gary Keramidas" wrote:

this is for listbox1 on a userform:

Private Sub UserForm_Activate()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name < ActiveSheet.Name Then
Me.ListBox1.AddItem ws.Name
End If
Next
End Sub

--


Gary

"travis" wrote in message
...
Hi,

I'm not sure how to go about this one...

I want a listbox control in a userform launched from
spreadsheetone.xls to display the names of all the other
spreadsheets. i.e. the user can select any workbook in the open
workbooks collection, but spreadsheetone.xls won't be in that list
(because the form is part of an exporting routine to copy charts and
tables from spreadsheetone.xls to a new worksheet in whichever open
workbook the user selects).

How do I put the names of all open workbooks into a listbox?

Travis

Populate a list box with all active workbooks except the curre
 

you're probably right, my mistake.

--


Gary

"Jim Thomlinson" wrote in message
...
I was under the impression that Travis wanted the workbooks and not the
worksheets...
--
HTH...

Jim Thomlinson


"Gary Keramidas" wrote:

this is for listbox1 on a userform:

Private Sub UserForm_Activate()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name < ActiveSheet.Name Then
Me.ListBox1.AddItem ws.Name
End If
Next
End Sub

--


Gary

"travis" wrote in message
...
Hi,

I'm not sure how to go about this one...

I want a listbox control in a userform launched from
spreadsheetone.xls to display the names of all the other
spreadsheets. i.e. the user can select any workbook in the open
workbooks collection, but spreadsheetone.xls won't be in that list
(because the form is part of an exporting routine to copy charts and
tables from spreadsheetone.xls to a new worksheet in whichever open
workbook the user selects).

How do I put the names of all open workbooks into a listbox?

Travis

Populate a list box with all active workbooks except the currentone.
 

Thanks for your answers Jim and Gary. Just what I was needing.

Travis

Populate a list box with all active workbooks except the currentone.
 

On Oct 3, 4:09*am, "Gary Keramidas" <GKeramidasAtMsn.com wrote:
this is for listbox1 on a userform:

Private Sub UserForm_Activate()
* * * Dim ws As Worksheet
* * * For Each ws In ThisWorkbook.Worksheets
* * * * * * If ws.Name < ActiveSheet.Name Then
* * * * * * * * * Me.ListBox1.AddItem ws.Name
* * * * * * End If
* * * Next
End Sub

--

Gary

Gary, your contribution was useful to me because shortly after needing
to populate a listbox with all the open workbooks I needed to populate
a listbox with all the sheets in a specified workbook.

But, how would I modify the above code to get the worksheets from a
different workbook other than ThisWorkbook?

I have a public variable called "projectionbook" which contains the
name of a newly initiated workbook which I intend to write to. It
contains a value like "book8".

I got the result I wanted by using the following code:

Windows(projectionbook).Activate
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Me.lstSheetsInProjection.AddItem ws.Name
Next

But why doesn't something like the following work? How should I use
the projectionbook variable in a more direct way instead of activating
it then calling it as the activeworkbook?

Dim ws As Worksheet
For Each ws In projectionbook.Worksheets
Me.lstSheetsInProjection.AddItem ws.Name
Next

Travis

Populate a list box with all active workbooks except the current one.
 

it depends on how you declare the variable projectionbook:

as a string:

Private Sub UserForm_Activate2()
Dim ws As Worksheet
Dim projectionbook As String
projectionbook = Workbooks("yourworkbook.xls").Name 'change the name

For Each ws In Workbooks(projectionbook).Worksheets
Me.lstSheetsInProjection.AddItem ws.Name
Next
End Sub

as a workbook:

Private Sub UserForm_Activate()

Dim ws As Worksheet
Dim projectionbook As Workbook
Set projectionbook = Workbooks("yourworkbook.xls") 'change the name

For Each ws In projectionbook.Worksheets
Me.lstSheetsInProjection.AddItem ws.Name
Next
End Sub

--

Gary
Excel 2003


"travis" wrote in message
...
On Oct 3, 4:09 am, "Gary Keramidas" <GKeramidasAtMsn.com wrote:
this is for listbox1 on a userform:

Private Sub UserForm_Activate()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name < ActiveSheet.Name Then
Me.ListBox1.AddItem ws.Name
End If
Next
End Sub

--

Gary

Gary, your contribution was useful to me because shortly after needing
to populate a listbox with all the open workbooks I needed to populate
a listbox with all the sheets in a specified workbook.

But, how would I modify the above code to get the worksheets from a
different workbook other than ThisWorkbook?

I have a public variable called "projectionbook" which contains the
name of a newly initiated workbook which I intend to write to. It
contains a value like "book8".

I got the result I wanted by using the following code:

Windows(projectionbook).Activate
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Me.lstSheetsInProjection.AddItem ws.Name
Next

But why doesn't something like the following work? How should I use
the projectionbook variable in a more direct way instead of activating
it then calling it as the activeworkbook?

Dim ws As Worksheet
For Each ws In projectionbook.Worksheets
Me.lstSheetsInProjection.AddItem ws.Name
Next

Travis