Combining Data Validation

[energman58]

Hello excellers,

In my model allowable data in a cell is blank, zero or any positive number. I can use data validation to make sure that this is the case - no problem. However, blank and zero are not treated the same in the model - blank effectively says "no restriction" and allows the model freedom to set this value whereas zero says the value of this parameter must be zero (in practice any number including zero sets the maximum value for the parameter and negative numbers are not allowed). Therefore I want to make sure that when someone enters zero they really do mean zero and not blank.

What I would ideally like to do is use data validation to make sure they enter an allowable value but in the case they enter a zero pop up a warning saying "Do you really mean zero here?" (blank is kind of the default condition so I am not too worried about folk entering a blank when they mean zero). I can do this with VBA but as there are many places (thousands) in the model where they can make this mistake it is going to be a real pain implementing it using WorksheetChange event compared to a data validation solution if this is possible.

All suggestions (very) gratefully received!

 

[Roger Govier]

Hi

You could use DV and implement the Input Message >Show Input message when cell is selected.

Put your message there telling the User that leaving the cell empty (the default condition will mean ......
otherwise enter a value between 0 and XXX

 

[energman58]

Yes that is where I had got to as well. However I just had a thought prompted by your message - as negative values are generally forbidden as user inputs in the model I can catch that using a Worksheet_Change procedure for the input sheets (as I only need to check that the entered value is non-negative that should be easy enough to implement) and I can then use the DV (which can be set to ignore blanks) to pop up the warning if zero is entered!

Thanks

 

[Roger Govier]

Hi
You could do it all with the DV if you wanted with
Custom
=">="&0

 

[energman58]

The data validation runs before the worksheet change event so it is not ideal but it works - you get a warning from the DV and then the worksheet change procedure kicks out any negatives. The only option to get it completely right would be to use the worksheet change event for everything (both restricting the entry to positive numbers and sending a warning for zero) but that defeats the easy copy-paste benefits of the DV. In the end I may implement the proper solution but as a stop gap I will use this.

Thanks for your help anyway