excel - 如何将动态更改的验证列表重置为第一项？

Question

我正在使用以下代码动态更改 Excel 中的验证列表。这个想法是，当另一个值发生变化时，列表应该，例如，包含三个项目而不是五个(前两个消失了):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ToS, CoC As String
Dim HoC, HoR As Single

ToS = Range("B4").Value
CoC = Range("B12").Value
HoC = Range("B10").Value
HoR = Range("B11").Value

With Range("B6")
    With .Validation
        .Delete
        If ToS = "CMSA" Then
            If HoC <= 7.6 Then
                If HoR > 10.7 Then
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$3:$V$3"
                Else
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$1:$V$3"
                End If
            ElseIf CoC = "III" Or CoC = "IV" Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$3:$V$3"
            Else
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$1:$V$3"
            End If
        ElseIf ToS = "ESFR" Then
            If HoR > 10.7 And HoR <= 12.2 Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$7:$V$9"
            ElseIf HoR > 9.1 And HoR <= 9.8 And HoC > 6.1 And HoC <= 7.6 Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$6:$V$7"
            ElseIf HoR > 12.2 And HoR <= 13.7 And HoC > 7.6 And HoC <= 12.2 Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$7:$V$9"
            Else
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$6:$V$9"
            End If
        Else
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$6:$V$9"
        End If
    End With
End With

End Sub

问题是我希望它自动重置为列表的第一项。该列表正在从同一工作表中的一行中的其他一些单元格中获取数据。

我怎么能那样做？

我需要在代码中添加什么？

我尝试在 ...formula1:= 之后添加命令类似于 Range("B6").Value= "the value I want"但它不起作用。

Answer 1

尽管我不得不同意“Eh”级培根和斯科特的评论，但我认为所寻求的只是如下所示:

(也就是说我想你只是想添加一行来将值更​​改为数据验证列表中的第一个值)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ToS, CoC As String
Dim HoC, HoR As Single

ToS = Range("B4").Value
CoC = Range("B12").Value
HoC = Range("B10").Value
HoR = Range("B11").Value

With Range("B6")
    With .Validation
        .Delete
        If ToS = "CMSA" Then
            If HoC <= 7.6 Then
                If HoR > 10.7 Then
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$3:$V$3"
                    .Value=Range("$V$3").Value
                Else
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$1:$V$3"
                    .Value=Range("$V$1").Value
                End If
            ElseIf CoC = "III" Or CoC = "IV" Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$3:$V$3"
                    .Value=Range("$V$3").Value
            Else
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$1:$V$3"
                    .Value=Range("$V$1").Value
            End If
        ElseIf ToS = "ESFR" Then
            If HoR > 10.7 And HoR <= 12.2 Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$7:$V$9"
                    .Value=Range("$V$7").Value
            ElseIf HoR > 9.1 And HoR <= 9.8 And HoC > 6.1 And HoC <= 7.6 Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$6:$V$7"
                    .Value=Range("$V$6").Value
            ElseIf HoR > 12.2 And HoR <= 13.7 And HoC > 7.6 And HoC <= 12.2 Then
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$7:$V$9"
                    .Value=Range("$V$7").Value
            Else
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$6:$V$9"
                    .Value=Range("$V$6").Value
            End If
        Else
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$V$6:$V$9"
                    .Value=Range("$V$6").Value
        End If
    End With
End With

End Sub

但我再次认为你最好按照他们的建议处理数据验证，也许只是使用 vba 来更改单元格的值。