How to embed creating dropdown lists inside a select case on Excel?

Question

I can't figure out how to create dropdown lists depending on a certain cell value. I have these two functions but don't know how to merge them.

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

The dropdowns to create are on another cell, the same only cell.

score 0 · Answer 1 · answered Nov 13 '20 at 01:41

Something like this should work:

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

It doesn't seem to work, don't know why. I tried putting the two subs, one under a sheet and the other that creates the dropdown list under a macro but still doesn't work. — BrokenStrings, Nov 13 '20 at 09:58
try commenting out the error handler and see if there's an error. Just be aware you may need to re-enable events if it crashes before `Application.EnableEvents = True` — Tim Williams, Nov 13 '20 at 16:28
It's working fine for me (I commented out the call to `EmergencyError` but otherwise it runs as-is) — Tim Williams, Nov 13 '20 at 16:50

How to embed creating dropdown lists inside a select case on Excel?

1 Answers1