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

[travis]

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

 

[Gary Keramidas]

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

 

[Jim Thomlinson]

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

 

[Jim Thomlinson]

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

 

[Gary Keramidas]

you're probably right, my mistake.

--


Gary

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

 

[travis]

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

Travis

 

[travis]

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, 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

 

[Gary Keramidas]

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

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, 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