I have a user form with a calculated amount column that contains
formulas.
My users inadvertantly keep deleting these cells.
If I lock them and enable protection, they don't let the program change
calculated values.

My idea was:
To move these cells out of site,
Trigger an ON CHANGE EVENT to run a macro which will copy and PASTE
VALUES ONLY back to the form.

Problem is that the ON CHANGE EVENT only triggers on changes in the
cells formula, not in its calculated displayed value.

Any ideas?

In your program, unprotect the sheet, change values, the protect it again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I have a user form with a calculated amount column that contains
formulas.
My users inadvertantly keep deleting these cells.
If I lock them and enable protection, they don't let the program change
calculated values.

My idea was:
To move these cells out of site,
Trigger an ON CHANGE EVENT to run a macro which will copy and PASTE
VALUES ONLY back to the form.

Problem is that the ON CHANGE EVENT only triggers on changes in the
cells formula, not in its calculated displayed value.

Any ideas?

Sheet is unprotected
Problem is that the ON CHANGE EVENT only triggers on changes in the
cells formula, not in its calculated displayed value.

Bob Phillips wrote:
In your program, unprotect the sheet, change values, the protect it again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I have a user form with a calculated amount column that contains
formulas.
My users inadvertantly keep deleting these cells.
If I lock them and enable protection, they don't let the program change
calculated values.

My idea was:
To move these cells out of site,
Trigger an ON CHANGE EVENT to run a macro which will copy and PASTE
VALUES ONLY back to the form.

Problem is that the ON CHANGE EVENT only triggers on changes in the
cells formula, not in its calculated displayed value.

Any ideas?

No I am saying forget your alternative, lock the cells and manage it as I
suggested.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Sheet is unprotected
Problem is that the ON CHANGE EVENT only triggers on changes in the
cells formula, not in its calculated displayed value.

Bob Phillips wrote:
In your program, unprotect the sheet, change values, the protect it
again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I have a user form with a calculated amount column that contains
formulas.
My users inadvertantly keep deleting these cells.
If I lock them and enable protection, they don't let the program
change
calculated values.

My idea was:
To move these cells out of site,
Trigger an ON CHANGE EVENT to run a macro which will copy and PASTE
VALUES ONLY back to the form.

Problem is that the ON CHANGE EVENT only triggers on changes in the
cells formula, not in its calculated displayed value.

Any ideas?

Thanks
Sometimes I can't see the obvious.


Bob Phillips wrote:
No I am saying forget your alternative, lock the cells and manage it as I
suggested.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Sheet is unprotected
Problem is that the ON CHANGE EVENT only triggers on changes in the
cells formula, not in its calculated displayed value.

Bob Phillips wrote:
In your program, unprotect the sheet, change values, the protect it
again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I have a user form with a calculated amount column that contains
formulas.
My users inadvertantly keep deleting these cells.
If I lock them and enable protection, they don't let the program
change
calculated values.

My idea was:
To move these cells out of site,
Trigger an ON CHANGE EVENT to run a macro which will copy and PASTE
VALUES ONLY back to the form.

Problem is that the ON CHANGE EVENT only triggers on changes in the
cells formula, not in its calculated displayed value.

Any ideas?