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

stuckagain22

Board Regular
Joined
Aug 4, 2006
Messages
182
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?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mbb4331

Board Regular
Joined
Dec 22, 2005
Messages
152
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,505
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 4, 2006
Messages
182
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,505
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,940
Messages
5,703,285
Members
421,289
Latest member
fbohlandt

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