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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
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,126,924
Messages
5,621,629
Members
415,848
Latest member
LCC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top