如何在Excel的选择案例中嵌入创建下拉列表

Question

我不知道如何根据某个单元格值创建下拉列表。我有这两个函数，但不知道如何将它们合并。

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C14")) Is Nothing Then
Select Case Range("C14")
Case "Emergency": EmergencyError
Range("C18") = "No"
'Case "Basic":  ??
'Stops Select Case statement
End Select
End If
End Sub

Sub BasicList()
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=DONNEES!$A$4:$A$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

要创建的下拉列表在另一个单元格上，也就是同一个单元格。

Answer 1

这样的东西应该可以工作：

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Intersect(Target, Me.Range("C14"))

On Error GoTo haveError

If Not rng Is Nothing Then
Application.EnableEvents = False 'don't re-trigger this event
Select Case rng.Value
Case "Emergency"
EmergencyError
Me.Range("C18") = "No"
Case "Basic"
BasicList rng.Offset(0, 1) 'puts a validation list in D14
End Select
End If
haveError:
Application.EnableEvents = True 'make sure to re-enable events in case of an error

End Sub
'create a validation list in range "c"
Sub BasicList(c As Range)
With c.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=DONNEES!$A$4:$A$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub