There are 2 Listboxes - Listbox1 and Listbox2
I have a code that populates Listbox1 with workbooks from a folder. Clicking on any workbook will display all relevant sheets in Listbox2.
Private Sub ListBox1_Click()
Dim idx As Long, sName As String
Dim bk As Workbook, sh As Worksheet
idx = ListBox1.ListIndex
sName = ListBox1.List(idx)
Application.DisplayAlerts = False
Set bk = Workbooks.Open("D:\Counts\" & sName)
Application.DisplayAlerts = True
For Each sh In bk.Worksheets
ListBox2.AddItem sh.Name
Next
bk.Close SaveChanges:=False
End Sub
'====================================
Private Sub UserForm_Activate()
Dim DIRECTORY As String
'Clear Listboxes
ListBox1.Clear
'list directories
DIRECTORY = Dir("D:\Counts\*.xls", vbNormal)
Do Until DIRECTORY = ""
'add filename to listbox
ListBox1.AddItem DIRECTORY
DIRECTORY = Dir()
Loop
End Sub
Problem -
The code keeps adding sheets to Listbox2
Requirement -
I want the code to display sheets of selected workbook one at a time.
So, if I click on first workbook, the code should display sheets relevant to first workbook. If I click on second workbook, the code should display sheets relevant to second workbook only, so on and so forth.
What part of the code should be changed to achieve this.
