If statement resulting in list choice

[Sloopy]

Can you help me write an if statement: If A2>0 then the user must choose a
letter provided for them in a dropdown list in A3 otherwise A3 is blank.

 

[Pete_UK]

I'm not sure what you want. Can't you just put a drop-down in cell A3?

Please try to explain more clearly what you have, what cells you are using,
and what you want to achieve.

Pete

 

[Sloopy]

There is a drop down list in A3 but the user should only choose from the drop
list in A3 if the value in A2 is greater than 0. If the value in A2 is blank
then A3 should be blank and the user should not choose from the drop down
list. I hope that makes more sense?

 

[Rick Rothstein]

You will need to use VB event code to control the activation/deactivation of
the validation list. The code below will do that, but I'm not exactly sure
how to remove the displayed dropdown arrow that appears when A2 is not set
to a number greater than zero... the visible dropdown arrow that is shown
for this condition is not active though and any entry is permitted for it
(data restriction to the list is only active when A2 is a number greater
than zero)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim V As Range
If Target.Address = "$A$2" Then
With Range("A3")
On Error GoTo CleanUp
Application.EnableEvents = False
.Validation.Delete
If IsNumeric(Target.Value) Then
If Target.Value > 0 Then
.Value = ""
.Validation.Add Type:=xlValidateList, _
Formula1:="Item 1,Item 2,Item 3,etc."
End If
End If
End With
End If
CleanUp:
Application.EnableEvents = True
End Sub

To install this event code, right click the worksheet tab and select View
Code from the popup menu that appears, then copy/paste the above code into
the code window that appeared. That is it, change the value of A2 and then
try entering things in A3.

 

[Sloopy]

Thank you for your help - that's way above my head for sure but I'll take
some time to see if I can get it to work. Thanks again.

 

[Rick Rothstein]

Just to be clear about the list of items to be displayed in the dropdown,
you assign them in this line...

.Validation.Add Type:=xlValidateList, _
Formula1:="Item 1,Item 2,Item 3,etc."

The list is comma separated and do *not* put any spaces around the commas to
"neaten things up. Also, make sure you keep the space/underline after the
comma at the end of the first line... it forces VB to consider the two lines
as a single executable statement.