excel drop down box used conditionally based on previous input

[RGS]

I want a cell to have a drop down box to select from only when a prior cell
entry is true. Otherwise the cell will be blank, no drop down box, waiting
for the users input.

I appreciate the assistance.

Regards,

RGS

 

[Shane Devenshire]

Hi,

You would probably need to do that with VBA triggered by an Change event:

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:A10"))
If Not isect Is Nothing Then
'Your code here:
If target = True Then
Range("F1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=mylist"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
End If
End Sub

Here the A1:A10 is the trigger cell, meaning the one that determines whether
there will be data validation or not in you other cell. Record the adding of
the data validation and the removing of the data validiation and stick them
in above. I have put in a quick sample.

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.