Drop Down List Help

[OscartheDog]

Hi!

I posted something similar not that long ago but I have a follow-up question.

Originally I was looking for a way to have a drop-down list only appear based on what another cell said. I have a method of doing this now which I've attached below. My new question is, can this be done multiple times in the same Private Sub? I have a couple more options for users to choose on the same sheet and ideally, I want the same option. So for example, if there was a question in cell C18 asking if it was a regular repeat date and they selected 'Yes', I would like G17 to show a drop-down to show 'Daily', 'Weekly', etc. If they select 'No' I don't want a drop-down to be available.

Is there a way to add more than one of these:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
If Range("A1") = "TBC" Then
With Range("B1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Sheet2!F1:F4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
With Range("B1").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With



End If
End If

End Sub

Thanks!

OscarPup

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address = "$A$1" Then
      If Range("A1") = "TBC" Then
         With Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="=Sheet2!F1:F4"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
         End With
      Else
         Range("B1").Validation.Delete
      End If
   ElseIf Target.Address = "$C$18" Then
      If Target.Value = "Yes" Then
         With Range("G17").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="Daily,Weekly,etc"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
         End With
      Else
         Range("G17").Validation.Delete
      End If
   End If

End Sub