I have an Excel 2000 system which uses a control worksheet to allow users to
enter various options, and make selections from dropdown lists. When they
make these entries, the code in my worksheet_change event handler formats
other protected worksheets in the workbook by inserting and deleting various
rows and columns and then populating them with data from a database.

To save themselves some work my users have got into the habit of copying and
pasting cells and ranges in the control sheet, rather than selecting values
individually from each cell.

My problem is that as soon I unprotect any worksheet in worksheet_change,
Excel seems to perform an implicit Application.CutCopyMode=false which clears
the copied range. (It also does this if you unprotect sheets yourself using
Tools/Protection/Unprotect Sheet.) This is annoying my users who complain
that they have to keep re-selecting and copying things all the time.

Is there any way to stop Excel from clearing selections when unprotecting
worksheets, or at least get the copied range back from the clipboard so I can
restore it after my macro runs?

You might try saving the workbook before the unprotect sheets is run.
However, it seems to me that the users are not really saving themselves any
work by trying to circumvent proper use of the workbook.

Mike F
"greyflame" wrote in message
...
I have an Excel 2000 system which uses a control worksheet to allow users
to
enter various options, and make selections from dropdown lists. When they
make these entries, the code in my worksheet_change event handler formats
other protected worksheets in the workbook by inserting and deleting
various
rows and columns and then populating them with data from a database.

To save themselves some work my users have got into the habit of copying
and
pasting cells and ranges in the control sheet, rather than selecting
values
individually from each cell.

My problem is that as soon I unprotect any worksheet in worksheet_change,
Excel seems to perform an implicit Application.CutCopyMode=false which
clears
the copied range. (It also does this if you unprotect sheets yourself
using
Tools/Protection/Unprotect Sheet.) This is annoying my users who complain
that they have to keep re-selecting and copying things all the time.

Is there any way to stop Excel from clearing selections when unprotecting
worksheets, or at least get the copied range back from the clipboard so I
can
restore it after my macro runs?

Is there really no way to stop Excel from clearing a copied selection when
unprotecting a sheet? Or restoring it afterwards? This seems like a really
major fault in Excel.

"Mike Fogleman" wrote:

You might try saving the workbook before the unprotect sheets is run.
However, it seems to me that the users are not really saving themselves any
work by trying to circumvent proper use of the workbook.

Mike F
"greyflame" wrote in message
...
I have an Excel 2000 system which uses a control worksheet to allow users
to
enter various options, and make selections from dropdown lists. When they
make these entries, the code in my worksheet_change event handler formats
other protected worksheets in the workbook by inserting and deleting
various
rows and columns and then populating them with data from a database.

To save themselves some work my users have got into the habit of copying
and
pasting cells and ranges in the control sheet, rather than selecting
values
individually from each cell.

My problem is that as soon I unprotect any worksheet in worksheet_change,
Excel seems to perform an implicit Application.CutCopyMode=false which
clears
the copied range. (It also does this if you unprotect sheets yourself
using
Tools/Protection/Unprotect Sheet.) This is annoying my users who complain
that they have to keep re-selecting and copying things all the time.

Is there any way to stop Excel from clearing selections when unprotecting
worksheets, or at least get the copied range back from the clipboard so I
can
restore it after my macro runs?