combo box multiple workbooks / sheets
 

Hi,

I'm using Excel 2002 to create a report menu for a client. As an
example, they have 3 workbooks each with 3 sheets.

I would like to create a combo box on a user form that will be visible
from each sheet. The combo box will list all open workbooks/sheets
like this:

Workbook1
sheet 1
sheet 2
sheet 3
Workbook2
sheet 1
sheet 2
sheet 3
Workbook3
sheet 1
sheet 2
sheet 3


I know how to do the userform. And I understand the code for getting
the sheetnames from one workbook. But I need a little direction in
looping thru all open workbooks and getting the sheet names and
separating them by workbook in the combo box.

Note: The workbooks are hidden until a user selects one of the reports
(sheets).

Thank you,

Terry

combo box multiple workbooks / sheets
 

Maybe something like this:

Private Sub UserForm_Initialize()
Dim wk As Workbook
Dim sht As Worksheet
For Each wk In Application.Workbooks
If wk.Name < "PERSONAL.XLS" Then
Me.ComboBox1.AddItem wk.Name
For Each sht In wk.Worksheets
Me.ComboBox1.AddItem sht.Name
Next sht
End If
Next wk
End Sub


Hope this helps
Rowan

wrote:
Hi,

I'm using Excel 2002 to create a report menu for a client. As an
example, they have 3 workbooks each with 3 sheets.

I would like to create a combo box on a user form that will be visible
from each sheet. The combo box will list all open workbooks/sheets
like this:

Workbook1
sheet 1
sheet 2
sheet 3
Workbook2
sheet 1
sheet 2
sheet 3
Workbook3
sheet 1
sheet 2
sheet 3


I know how to do the userform. And I understand the code for getting
the sheetnames from one workbook. But I need a little direction in
looping thru all open workbooks and getting the sheet names and
separating them by workbook in the combo box.

Note: The workbooks are hidden until a user selects one of the reports
(sheets).

Thank you,

Terry

combo box multiple workbooks / sheets
 

Thank you Rowan. That helped.

Terry

combo box multiple workbooks / sheets
 

You're welcome.

wrote:
Thank you Rowan. That helped.

Terry