工作表中的Active X组合框

Question

我在ComboBox1的工作表中使用Active X ComboBox，我已经列出了工作表名称，这很好。

我尝试在ComboBox1中选择的表的基础上添加ComboBox2中的数据。

如果我从下拉列表中选择ComboBox1中的工作表名称，则ComboBox2需要自动填充所选工作表中的数据，并且一旦我单击命令按钮，则ComboBox1中工作表和ComboBox2中的详细信息需要同时选择。请在这个话题上帮助我，这将对我开发我的项目更有帮助。

简单地说，我的问题是，我需要在ComboBox1中选择工作表，并需要在ComboBox2中选择范围。一旦我单击命令按钮，特定的工作表和范围需要一次选择。

以下代码：

Private Sub CommandButton1_Click()
Dim sName As String
Dim x As Integer
Dim Range As Range
Dim Sh As Worksheet
For x = 3 To Sheets.Count
Sheets(x).Visible = False
Next x
sName = ComboBox1.Value
With ActiveWorkbook.Sheets(sName)

.Visible = True
.Activate
End With
End Sub
Private Sub Worksheet_Activate()
Dim Sh As Worksheet
Dim sName As String
Dim x As Integer
Me.ComboBox1.Clear
For Each Sh In ThisWorkbook.Worksheets
Me.ComboBox1.AddItem Sh.Name
Next Sh
Exit Sub
End Sub
Private Sub ComboBox2_Change()
Dim Sh As Worksheet
Dim sName As String
sName = ComboBox1.Value
If ComboBox1.Value Then
Sheets(sName).Activate
Set Sh = Worksheets("sName")
If ActiveSheet.Name = Sh.Name Then
ComboBox2.ListFillRange = "sName"
Else
Exit Sub
End If
End Sub

Answer 1

请尝试下一种方法：

1. 复制同一工作表模块中的下一个事件代码。它将填充ListBox1：

Private Sub Worksheet_Activate()
Dim sh As Worksheet

Application.EnableEvents = False
ActiveSheet.OLEObjects("Listbox1").Object.Clear
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.OLEObjects("Listbox1").Object.AddItem sh.Name
Next
Application.EnableEvents = True
End Sub

2. 复制下一个代码(ListBox1_Change事件(。它将填充ListBox1中所选工作表的ListBox2的范围：

Private Sub ListBox1_Change()
Dim lst1 As MSForms.listBox, lst2 As MSForms.listBox, ws As Worksheet
Dim lastRow As Long, arrNam As Variant, I As Long, k As Long

Set lst1 = ActiveSheet.OLEObjects("Listbox1").Object
Set lst2 = ActiveSheet.OLEObjects("Listbox2").Object

If lst1.ListIndex = -1 Then Exit Sub
Set ws = Worksheets(lst1.Value)
lastRow = ws.Range("A" & rows.count).End(xlUp).row
If lastRow = 1 Then
If ws.Range("A1").Value = "" Then
MsgBox "Nothing on A:A column of sheet " & ws.Name: Exit Sub
Else
With lst2
.Clear
.ColumnCount = 2
.AddItem ws.Range("A1").Value
.list(0, 1) = 1
End With
Exit Sub
End If
End If
ReDim arrNam(1 To 2, 1 To lastRow)
For I = 1 To lastRow
If ws.Range("A" & I).Value <> "" Then
k = k + 1
arrNam(1, k) = ws.Range("A" & I).Value
arrNam(2, k) = I 'the row to be selected
End If
Next
ReDim Preserve arrNam(1 To 2, 1 To k)
With lst2
.ListFillRange = ""
.ColumnWidths = "100;0"
.Clear
.ColumnCount = 2 ' Worksheets(lst1.Value).Range(rngAddress).Columns.count
.list = Application.Transpose(arrNam)
End With
End Sub

3. 在同一工作表模块中复制下一个Sub代码。它将从列表框1中选择的工作表中选择列表框2的所有范围：

Sub SelectSheetNames()
Dim lst1 As MSForms.listBox, lst2 As MSForms.listBox, i As Long
Dim rngUnion As Range

Set lst1 = ActiveSheet.OLEObjects("Listbox1").Object
Set lst2 = ActiveSheet.OLEObjects("Listbox2").Object
Worksheets(lst1.Value).Activate
For i = 0 To lst2.ListCount - 1
If rngUnion Is Nothing Then
Set rngUnion = Worksheets(lst1.Value).Range("A" & lst2.list(i, 1))
Else
Set rngUnion = Union(rngUnion, Worksheets(lst1.Value).Range("A" & lst2.list(i, 1)))
End If
Next i
rngUnion.Select
End Sub

上面的Sub可以从按钮点击事件中调用。。。

重新审核：

我认为您将需要下一个listBox2更改事件。请在同一表单模块中复制下一个代码：

Private Sub ListBox2_Change()
Dim ws As Worksheet, lst1 As MSForms.listBox, lst2 As MSForms.listBox

Set lst1 = ActiveSheet.OLEObjects("Listbox1").Object
Set lst2 = ActiveSheet.OLEObjects("Listbox2").Object
Set ws = Worksheets(lst1.Value)
If lst2.ListIndex = -1 Then Exit Sub
MsgBox ws.Range("B" & lst2.list(lst2.ListIndex, 1))
End Sub

上面的代码将在MsgBox中显示您在列表框中选择的名称的B:B列的内容。。。

请测试并发送一些反馈。