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