vba - 单击第一个下拉列表时，将宏执行到第二个下拉列表

Question

问题

我有两个下拉列表，当我从第一个下拉列表中选择一个选项时，有没有办法自动选择第二个下拉列表？

下拉列表使用 2 个数据透视表的数据验证填充。

代码

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If Not Intersect(cell, Range("F9")) Is Nothing Then
       Call Sample_Click
    ElseIf Not Intersect(cell, Range("F10")) Is Nothing Then
       Call Sample2_Click
End If
Next cell
End Sub

sample _点击

Dim ACount As Integer
Dim Dept As String
Dim Func As String
Dim Pos As String

Range("F9").Select
Dept = Trim(ActiveCell.Value)

Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Dept").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Dept").CurrentPage =    Dept

ACount = ActiveSheet.PivotTables("PivotTable1").RowRange.Cells.Count
ACount = ACount + 2

Sheets("Home").Select
    Range("F10").Select
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Sheet1!$A$4:$A$" & ACount
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
    ActiveCell.Formula = ""

问题
Sample2_Click与 Sample_Click 相同, 唯一的区别是 Range("")值(value)。
尽管我尝试了所有努力，但我遇到了错误。

Answer 1

使用 intersect 时，您不需要循环所有单元格......而且工作表应该是正确的......这样，您只需要:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, [F9]) Is Nothing Then
    [F10].Select
  ElseIf Not Intersect(Target, [F10]) Is Nothing Then
    [F11].Select
  End If
End Sub

...为我工作没有错误...