Drop Down List Help

OscartheDog

New Member
Joined
Dec 3, 2019
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,086
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,182
Messages
5,576,569
Members
412,733
Latest member
korayuyanik
Top