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

"Guy Normandeau" wrote:

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?

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.



"Mat P:son" wrote:

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

"Guy Normandeau" wrote:

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?

Have you tried

Application.Undo

To undo the changes...
--
HTH...

Jim Thomlinson


"Guy Normandeau" wrote:

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?

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

"Guy Normandeau" wrote:

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.



"Mat P:son" wrote:

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

"Guy Normandeau" wrote:

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?

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

"Jim Thomlinson" wrote:

Have you tried

Application.Undo

To undo the changes...
--
HTH...

Jim Thomlinson


"Guy Normandeau" wrote:

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?

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

--
Tim Williams
Palo Alto, CA


"Mat P:son" wrote in message ...
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

"Guy Normandeau" wrote:

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.



"Mat P:son" wrote:

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

"Guy Normandeau" wrote:

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?

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

"Tim Williams" wrote:

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

--
Tim Williams
Palo Alto, CA


"Mat P:son" wrote in message ...
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

"Guy Normandeau" wrote:

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.



"Mat P:son" wrote:

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

"Guy Normandeau" wrote:

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?

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


"Guy Normandeau" wrote in message
...
You cannot modify other columns/cells inside a function call from a cell.

"Tim Williams" wrote:

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

--
Tim Williams
Palo Alto, CA


"Mat P:son" wrote in message ...
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

"Guy Normandeau" wrote:

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.



"Mat P:son" wrote:

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

"Guy Normandeau" wrote:

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?

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.


"Guy Normandeau" wrote in message
...
You cannot modify other columns/cells inside a function call from a cell.

"Tim Williams" wrote:

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

--
Tim Williams
Palo Alto, CA


"Mat P:son" wrote in message ...
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

Perfect!

"Tim Williams" wrote:

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.


"Guy Normandeau" wrote in message
...
You cannot modify other columns/cells inside a function call from a cell.

"Tim Williams" wrote:

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

--
Tim Williams
Palo Alto, CA


"Mat P:son" wrote in message ...
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