Make a cell either display N/A or a drop down menu based on another cell

[JustHooch]

I have been looking around and can't seem to find exactly what I'm trying to do. I know I need a VBA script to accomplish my ask.

Cell B4 contains a drop down menu
Cell B5 is dependent on B4

• If B4 = blank then B5 = blank
• If B4 = "Special/Hard Ticket Event" then B5 = "N/A"
• if B4 is anything else then B5 = drop down list, name YN

Can someone help me with the VBA needed to get this done.

 

[Scott T]

Adjust the data validation code to match what you need I assumed you had a list in I3:I7

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
    If Range("B4") = "" Then
        Range("B5").Validation.Delete
        Range("B5").ClearContents
        
    ElseIf UCase(Range("B4")) = "SPECIAL/HARD TICKET EVENT" Then
        Range("B5").Validation.Delete
        Range("B5") = "N/A"
    Else
        'data valication here adjust to match what you need
        With Range("B5").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$I$3:$I$7"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If

End If

End Sub

 

[JustHooch]

This worked like a charm. Thank you very much.

 

[JustHooch]

What if I needed to swap the logic?

Cell B4 contains a drop down menu
Cell B5 is dependent on B4

• If B4 = blank then B5 = blank
• If B4 = "Special/Hard Ticket Event" then B5 =drop down list, location tab Dropdown Values C1:C2
• if B4 is anything else then B5 = "N/A"

 

[Scott T]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
    If Range("B4") = "" Then
        Range("B5").Validation.Delete
        Range("B5").ClearContents
        
    ElseIf UCase(Range("B4")) = "SPECIAL/HARD TICKET EVENT" Then
         'data valication here adjust to match what you need
        With Range("B5").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Dropdown Values '!$C$1:$C$2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Else
     Range("B5").Validation.Delete
        Range("B5") = "N/A"
End If

End If

End Sub

 

[JustHooch]

Thank you again.