combo box multiple workbooks / sheets

[tcnolan]

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

 

[Rowan Drummond]

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

 

[tcnolan]

Thank you Rowan. That helped.

Terry

 

[Rowan Drummond]

You're welcome.