Populating One Listbox from a second listbox

[Steven Taylor]

Hi,



I would really appreciate some help with the following:



1. I would like to create a user form that has two list boxes.

2. The first list box should provide a listing of all open excel workbooks.

3. The second list box should display the sheets of the workbook that

is selected in the first list box.

4. The code should prevent duplicate workbooks or worksheets from

being displayed in the list boxes.



I am working with Windows XP and Excel 2002.



Any advice on how to accomplished the above would be greatly appreciated.



Thanks,



Steve

 

[Leith Ross]

Hi,

I would really appreciate some help with the following:

1. I would like to create a user form that has two list boxes.

2. The first list box should provide a listing of all open excel workbooks.

3. The second list box should display the sheets of the workbook that

is selected in the first list box.

4. The code should prevent duplicate workbooks or worksheets from

being displayed in the list boxes.

I am working with Windows XP and Excel 2002.

Any advice on how to accomplished the above would be greatly appreciated.

Thanks,

Steve

Hello Steve,

This code should get you going. It assumes you have a UserForm named
UserForm1 with 2 ListBoxes on it named ListBox1, and ListBox2. When
the UserForm is Activated, ListBox1 is loaded with names of the Open
Workbooks. Once a Workbook has been selected, ListBox2 is loaded with
the all the sheet names in the choosen workbook.

'Begin Macro Code
Private Sub ListBox1_Click()

Dim Sht As Sheet
Dim Wkb As Workbook

ListBox2.Clear
Set Wkb = Workbooks(ListBox1.Value)

For Each Sht In Wkb.Sheets
ListBox2.AddItem Sht.Name
Next Sht

End Sub

Private Sub UserForm_Activate()

Dim Wkb As Workbook

For Each Wkb In Application.Workbooks
ListBox1.AddItem Wkb.Name
Next Wkb

End Sub
'End Macro Code...

Sincerely,
Leith Ross