从命名范围到默认值的 VBA 数据验证

Question

因此，我在一个单元格中进行了数据验证，该单元格根据另一个单元格中的用户选择更改为三个不同的命名范围之一。我需要的是当用户选择某个值时，即“选择 A”，数据验证不仅会更改为相应的命名范围，还会显示该范围内的第一个值。

目前我可以操纵代码来获取默认值，但是每次我尝试进行选择时它都会不断变回默认值。这甚至可能吗？下面是我当前使用 Worksheet_Change 事件为特定命名范围运行的代码

在这个例子中，我使用了命名范围 selection_a

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("$E$3").Value = "Selection A" Then
    With Range("L3:R4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=selection_a"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End If

默认值显示并不断变回:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("$E$3").Value = "Selection A" Then
    Range("$L$3").Value = Sheets("sheet2").Range("$M$4").Value    
    With Range("L3:R4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=selection_a"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End If

Answer 1

在下面的示例中，我在 Sheet1 的单元格 A1 中创建了一个验证列表。列表的条目在 K7 到 K9 中列出。

With Sheet1("A1").Validation
    .delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Sheet1!$K$7:$K$9"
End With

我会使用这种格式，因为我更喜欢它。现在回答你的问题:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Sheet1.Range("E3")) Then 'checks that E3 is the one that changed
        Application.EnableEvents = false
        If Range("$E$3").Value = "Selection A" Then 'list A
             With Sheet1("A1").Validation
               .delete
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                  xlBetween, Formula1:="=Sheet1!$K$7:$K$9" 'cells for list A
             End With
        ElseIf Range("$E$3").Value = "Selection B" Then' list B                 
             With Sheet1("A1").Validation
               .delete
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                  xlBetween, Formula1:="=Sheet1!$K$7:$K$9" 'cells for list B
             End With

        End if
    End if
    Application.EnableEvents = true

End sub

我很确定我把你的单元格弄错了，但是按照我放弃的例子把正确的单元格放在上面!
现在，当 E3 更改时，它将根据 E3 中的内容创建一个新列表。在我看来，E3 本身应该是一个验证列表，所有可能性都应该在工作表更改事件中表示出来。

编辑:我在看到拜伦的评论后添加了 EnableEvent = False ，没有它会在执行中触发。