How to make a List appear only when a condition is met?

[stuckagain22]

I was wondering if a there is a way to make a Validation List appear in a cell, only when a certain condition is met (ie: dependant on a value in a different cell.) If the condition is not met, then the cell would contain/perform a formula.

I have gone through "the famous" thread talking about conditional lists, however in that thread, the end result is that there is always a list in the thread. I don't want a list to appear if the condition is not met. Is there any way to do this?

 

[mbb4331]

What about a WorksheetChange event?

If the independent cell = "List", you could run the code to add validation to the dependent cell. If the independent cell = "Formula", you could add the formula to the cell.

 

[Peter_SSs]

stuckagain22

I have made these assumptions to provide a suggested solution:
1. The cell to contain the Validation or Formula is B1.
2. B1 is to be dependant on the contents of cell A1.
3. If A1 is blank then B1 is to be blank.
4. If A1 = "Monday" then B1 contains data Validation drawn from F1:F4
5. If A1 is anything else then B1 contains the formula =D1*2

Given the above, try this Worksheet_Change event code as a starting point:
<font face=Courier New>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Address <> "$A$1" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    Range("B1").ClearContents  <SPAN style="color:#007F00">'Not sure if you want this action</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Value = "Monday" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Adjust text/value to suit</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$F$1:$F$4" <SPAN style="color:#007F00">'Adjust Validation list range to suit</SPAN>
            .IgnoreBlank = <SPAN style="color:#00007F">True</SPAN>
            .InCellDropdown = <SPAN style="color:#00007F">True</SPAN>
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = <SPAN style="color:#00007F">True</SPAN>
            .ShowError = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">ElseIf</SPAN> Target.Value <> "" <SPAN style="color:#00007F">Then</SPAN>
        Range("B1").Formula = "=D1*2" <SPAN style="color:#007F00">'Adjust formula to suit</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

 

[stuckagain22]

Thanks Peter. I was hoping there would be a way to do it without code as I am not familiar with it at all. I should easily be able to throw that code into my sheet though. Thanks.

 

[erik.van.geit]

Hi,

this solution without code works for me

in the validation list-source type this kinda formula
=IF($A$1="yourstring";$B$2:$B$10;"")
if A1 = yourstring, then the source will be "$B$2:$B$10", else empty

rereading your initial post this might not be complete, but anyway I found this intresting enough to post and perhaps you can use this as a start ...

kind regards,
Erik

 

[Peter_SSs]

Thanks Peter. I was hoping there would be a way to do it without code as I am not familiar with it at all. I should easily be able to throw that code into my sheet though. Thanks.

If I have undestood correctly, then I don't think you can do what you want without VBA code. Post back if you need help with how to implement the code.