Reverting to previous values

[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?

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Have you tried

Application.Undo

To undo the changes...

 

[Guest]

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

 

[Guest]

This did in fact do what I wanted it to do. Thanks Jim!

 

[Tim Williams]

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...

 

[Guest]

You cannot modify other columns/cells inside a function call from a cell.

 

[Tim Williams]

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

 

[Tim Williams]

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.

 

[Guest]

Perfect!

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.