Reverting to previous values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm in the process of creating a validation function using the change event.
Is there a way that I can use VBA to revert the value in a cell or group of
cells the their original values if I find that the validation failed?
 
Hmmm, seems a little bit awkward to try to rollback a change that has already
happened. Is it somehow problematic in your particular situation to use the
normal data validation feature of Excel? If you can use it, it'll probably be
teh easiest solution (you can also control data validation for a Range object
programmatically, c.f., the Validation object)

Cheers,
/MP
 
I have too much validation to do on each cell. If I could use a custom
function insite thd validation, the the whole problem would be solved.

ei: '=MyFunction()'

Whenever I try to add this type of validation, Excel complains.
 
Yeah, would be nice if macros were allowed... Until that lovely day I suppose
we have to rely on the rather limited validation functionality that we've got.

Exactly how complicated is your validation? Is it still worth a try to hack
it together in the standard Excel formula language? Any pseudo-code or VBA
code available perhaps?

And although I'm sure you've been hunting high and low for info on
validation, here's a link with plenty of links that may be interesting:

http://www.mvps.org/dmcritchie/excel/validation.htm


Cheers,
/MP
 
Why not just put your custom function in another cell (with the entry cell as an input) and have the entry cell validation point to
the other cell ?

Works for me...
 
Yes, I know that.
But you can use a custom function as input to conditional formatting if you place it in an adjacent cell.

Tim
 
To expand on my previous reply....

If A1 is your data entry cell.
In B1 (or wherever) put "=IsOK(A1)"
For A1 choose Data > Validation > Custom and in Formula put "=B1"

My simple Test function:

Function IsOK(val) As Boolean
IsOK = (val <> "") And (val < 10)
End Function

Try it out. If you enter an invalid value (>=10) you will get the usual "Cancel/Retry" message.
The cell containing the validation formula would of course be hidden in the final version....


Tim.
 
Perfect!

Tim Williams said:
To expand on my previous reply....

If A1 is your data entry cell.
In B1 (or wherever) put "=IsOK(A1)"
For A1 choose Data > Validation > Custom and in Formula put "=B1"

My simple Test function:

Function IsOK(val) As Boolean
IsOK = (val <> "") And (val < 10)
End Function

Try it out. If you enter an invalid value (>=10) you will get the usual "Cancel/Retry" message.
The cell containing the validation formula would of course be hidden in the final version....


Tim.
Click to expand...
 
Back
Top