OscartheDog
New Member
- Joined
- Dec 3, 2019
- Messages
- 10
- Office Version
- 2016
- Platform
- 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:
Thanks!
OscarPup
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
OscarPup
Last edited: