UserForm Question, Excel 2000 & 2003

[jfcby]

Hello,

I created a form in workbook1 that will access all open workbooks.
This is my problem I have 2 listboxes; listbox1 is in the
UserForm_Initialize() event which adds all open workbooks to it. Now I
need, when a workbook in ListBox1 is clicked it adds all worksheets in
listbox2. Below is the following code I've tried and does not work:

'====>> This adds the workbook names to listbox1
Private Sub UserForm_Initialize()
Dim wb As Workbook
With ListBox1
For Each wb In Workbooks
.AddItem (wb.Name)
Next wb
.ListIndex = 1
End With
End Sub
'<<====

'====>> This does not add worksheets to listbox2 when workbook is
clicked in listbox1
'Tried Code1 also with the Change_event:
Private Sub ListBox1_Click()
Dim wks As Worksheet
With ListBox2
For Each wks In ActiveWorkbook.Worksheets
.AddItem (wks.Name)
Next
End With
End Sub

'Tried Code2 also with the Change_Event:
Private Sub ListBox1_Click()
Dim wks As Worksheet
ListBox2.AddItem(wks.Name) = ListBox1.Value
End Sub

Thank you for your help,
jfcby

 

[NickHK]

The listIndex property is 0 based, so I assumed you wanted the first (0) WB
selected not the second (1).
Also you need to .Clear listbox2 before you populate it each time

Try this:

Private Sub UserForm_Initialize()
Dim wb As Workbook

With ListBox1
For Each wb In Workbooks
.AddItem wb.Name
Next wb
.ListIndex = 0
End With
End Sub


Private Sub ListBox1_Click()
Dim wks As Worksheet
Dim WKS_Name As String

WKS_Name = ListBox1.List(ListBox1.ListIndex)

With ListBox2
.Clear
For Each wks In Workbooks(WKS_Name).Worksheets
.AddItem wks.Name
Next
End With
End Sub

NickHK

 

[jfcby]

The listIndex property is 0 based, so I assumed you wanted the first (0) WB
selected not the second (1).
Also you need to .Clear listbox2 before you populate it each time

Try this:

Private Sub UserForm_Initialize()
Dim wb As Workbook

With ListBox1
For Each wb In Workbooks
.AddItem wb.Name
Next wb
.ListIndex = 0
End With
End Sub

Private Sub ListBox1_Click()
Dim wks As Worksheet
Dim WKS_Name As String

WKS_Name = ListBox1.List(ListBox1.ListIndex)

With ListBox2
.Clear
For Each wks In Workbooks(WKS_Name).Worksheets
.AddItem wks.Name
Next
End With
End Sub

NickHK











- Show quoted text -

Hello NickHK,

The code works great!

Thank you for your help,
jfcby