Multiple VBA targets for data validation on a single sheet-CodePudding

There are similar posts relating to my question, but I have struggled to adapt them to my problem. I currently have the following code which works just fine:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = True

Set Target = Range("B4")

    If Target.Value = "Yes" Then
    
        Call DropDownListOn
    
    Else
        
        Call DropDownListOff
    
    End If
    
Application.EnableEvents = True

End Sub

and the macros that are being called are:

Option Explicit

Sub DropDownListOn()
    
    Sheet1.Activate
    
    Sheet1.Range("A5").Value = "Plot default probability"
    Sheet1.Range("B5").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Yes, No"
    
End Sub

and

Option Explicit

Sub DropDownListOff()
    
    Sheet1.Range("A5").Value = ""
    Sheet1.Range("B5").Validation.Delete
    
End Sub

Essentially what it does is as follows: Cell B4 is dropdown list. If "Yes" is chosen, then by calling the macro "DropDownListOn", it generates a new dropdown list in cell B5. This part works just fine; but, say, that once the dropdown list in cell B5 appears, I want to call another macro if B5 is chosen to be "Yes" (e.g., generating another dropdown list in cell B6). Given that only one target can be assigned to each sheet, this does not seem to be very straightforward. There seem to be suggestions for ways around this obstacle on Stackoverflow, but I struggle to adopt these to my own case. Any help and/or sample code will very much be appreciated.

CodePudding user response：

Please, try the next updated event:


Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address(0, 0) = "B4" Then
    If Target.Value = "Yes" Then
        Call DropDownListOn 'it let it creating the drop-down in "B5", TRIGGERING the change event
                            'but delete the charts   ONE BY ONE!
    Else
        Application.EnableEvents = False
           Call DropDownListOff 'the event is not triggered here.
           'if you want to also clear "B6", you should place such a code line in the above Sub...
        Application.EnableEvents = True
    End If
 ElseIf Target.Address(0, 0) = "B5" Then
     Application.EnableEvents = False
        'Place here the sub able to create the drop-down validation in "B6"
        'it let it creating something else, but without triggering the event
    Application.EnableEvents = Trud
 End If
End Sub