Listbox2仅显示来自Listbox1的选定工作簿的工作表 [英] Listbox2 to display sheets of selected workbooks only from Listbox1

问题描述

有2个列表框-Listbox1和Listbox2

There are 2 Listboxes - Listbox1 and Listbox2

我有一个代码，用文件夹中的工作簿填充Listbox1.单击任何工作簿将在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

问题-

代码不断将工作表添加到Listbox2

The code keeps adding sheets to Listbox2

要求-

我希望代码一次显示一张选定的工作簿.

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.

推荐答案

您需要一个

ListBox2.Clear

在您的ListBox1_Click()子句中，每个语句之前

In your ListBox1_Click() sub before the for each statement

这篇关于Listbox2仅显示来自Listbox1的选定工作簿的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！