Disable Cut, Paste, Drag & Drop in Protected Workbook

I have a protected Excel 2003 workbook with multiple protected worksheets.
Protection is set so that users can only select and edit unprotected cells.
The workbook is used by a number of people but it is not shared (each uses
their own).
Occasionally (I think unknowingly) someone will either cut and paste or drag
and drop a cells or cells which fouls hidden formulae. It doesn't cause
critical damage, but it's a nuisance when it happens. I was wondering if it
was possible to
a) disable cut and paste at the workbook or worksheet level; and
b) disable drag and drop at the workbook or worksheet level (not the
Application level)
Any help would be greatly appreciated. Thanks

Isn't that the purpose of protecting the sheets. Check to make sure when you
are protecting each sheet that it disables the actions you are referring to.
I believe xl2003 has a lot of options as to what you can and cannot allow
after protected.

"Andrew Stevens" wrote:

> I have a protected Excel 2003 workbook with multiple protected worksheets.
> Protection is set so that users can only select and edit unprotected cells.
> The workbook is used by a number of people but it is not shared (each uses
> their own).
> Occasionally (I think unknowingly) someone will either cut and paste or drag
> and drop a cells or cells which fouls hidden formulae. It doesn't cause
> critical damage, but it's a nuisance when it happens. I was wondering if it
> was possible to
> a) disable cut and paste at the workbook or worksheet level; and
> b) disable drag and drop at the workbook or worksheet level (not the
> Application level)
> Any help would be greatly appreciated. Thanks

Each sheet within a workbook has to be protected individually. When you
select the protection for that worksheet, a dialog box will pop up and you,
the workbook administator, can select to either lock all cells, or only allow
the option to select unlocked cells only. The catch to this is, I have found
it easiest to highlight the entire sheet (only the cells where the data is
stored) and right click the highlighted area. Click "Format Cells...", then
select the "Protection Tab". One there I always click through the "Locked"
box between the filled in box, the checked box, and the empty box. The I
click again until it has a check mark, thus indicating that all the cells are
indeed locked. then i go back to the sheet and select only the cells that I
want the user to be able to select. Follow the same process, only this time
leaving the "Locked" box with nothing in it (not filled in or checked). Go
back to your sheet and go to the protection option under Tools. Only check
the "Select Unlocked Cells" then hit ok and enter you password. Re-enter the
password and you are set. I hope this helps.

"Andrew Stevens" wrote:

> I have a protected Excel 2003 workbook with multiple protected worksheets.
> Protection is set so that users can only select and edit unprotected cells.
> The workbook is used by a number of people but it is not shared (each uses
> their own).
> Occasionally (I think unknowingly) someone will either cut and paste or drag
> and drop a cells or cells which fouls hidden formulae. It doesn't cause
> critical damage, but it's a nuisance when it happens. I was wondering if it
> was possible to
> a) disable cut and paste at the workbook or worksheet level; and
> b) disable drag and drop at the workbook or worksheet level (not the
> Application level)
> Any help would be greatly appreciated. Thanks

Thanks for your response. Perhaps I didn't word the question very well, I've
already done everything that you suggested. The problem isn't about with how
to protect and unprotect cells though, it's about users accidentally either
cutting or dragging cells within the unprotected area of the protected sheet
which then fouls formulae stored in protected cells within the sheet (and
workbook). I'm aware that you can disable the drag function in the
options/edit dialog box, or by using Application.CellDragAndDrop = False, but
this is at the application level and will impact on all open workbooks which
isn't what I want. It also doesn't stop users cutting and pasting. I've used
Excel for many years but I'm very much a novice when it comes to VB. I was
wondering if there was a VB solution to the problem.
Thanks again.

"Gee-off" wrote:

> Each sheet within a workbook has to be protected individually. When you
> select the protection for that worksheet, a dialog box will pop up and you,
> the workbook administator, can select to either lock all cells, or only allow
> the option to select unlocked cells only. The catch to this is, I have found
> it easiest to highlight the entire sheet (only the cells where the data is
> stored) and right click the highlighted area. Click "Format Cells...", then
> select the "Protection Tab". One there I always click through the "Locked"
> box between the filled in box, the checked box, and the empty box. The I
> click again until it has a check mark, thus indicating that all the cells are
> indeed locked. then i go back to the sheet and select only the cells that I
> want the user to be able to select. Follow the same process, only this time
> leaving the "Locked" box with nothing in it (not filled in or checked). Go
> back to your sheet and go to the protection option under Tools. Only check
> the "Select Unlocked Cells" then hit ok and enter you password. Re-enter the
> password and you are set. I hope this helps.
>
> "Andrew Stevens" wrote:
>
> > I have a protected Excel 2003 workbook with multiple protected worksheets.
> > Protection is set so that users can only select and edit unprotected cells.
> > The workbook is used by a number of people but it is not shared (each uses
> > their own).
> > Occasionally (I think unknowingly) someone will either cut and paste or drag
> > and drop a cells or cells which fouls hidden formulae. It doesn't cause
> > critical damage, but it's a nuisance when it happens. I was wondering if it
> > was possible to
> > a) disable cut and paste at the workbook or worksheet level; and
> > b) disable drag and drop at the workbook or worksheet level (not the
> > Application level)
> > Any help would be greatly appreciated. Thanks

You could place the following in the "ThisWorkbook" module

Private Sub Workbook_Activate()
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
End Sub

With the above code everytime the workbook is activated then no dragging of
cells is allowed. But as soon as the workbook is deactivated (i.e. another
workbook is activated) then the user will be able to drag and drop on that
workbook.

I don't know how to disable the cut and paste, but you may look at disabling
certain keys on the keyboard to prevent it, also take a look at the before
right click events.

JNW


"Andrew Stevens" wrote:

> Thanks for your response. Perhaps I didn't word the question very well, I've
> already done everything that you suggested. The problem isn't about with how
> to protect and unprotect cells though, it's about users accidentally either
> cutting or dragging cells within the unprotected area of the protected sheet
> which then fouls formulae stored in protected cells within the sheet (and
> workbook). I'm aware that you can disable the drag function in the
> options/edit dialog box, or by using Application.CellDragAndDrop = False, but
> this is at the application level and will impact on all open workbooks which
> isn't what I want. It also doesn't stop users cutting and pasting. I've used
> Excel for many years but I'm very much a novice when it comes to VB. I was
> wondering if there was a VB solution to the problem.
> Thanks again.
>
> "Gee-off" wrote:
>
> > Each sheet within a workbook has to be protected individually. When you
> > select the protection for that worksheet, a dialog box will pop up and you,
> > the workbook administator, can select to either lock all cells, or only allow
> > the option to select unlocked cells only. The catch to this is, I have found
> > it easiest to highlight the entire sheet (only the cells where the data is
> > stored) and right click the highlighted area. Click "Format Cells...", then
> > select the "Protection Tab". One there I always click through the "Locked"
> > box between the filled in box, the checked box, and the empty box. The I
> > click again until it has a check mark, thus indicating that all the cells are
> > indeed locked. then i go back to the sheet and select only the cells that I
> > want the user to be able to select. Follow the same process, only this time
> > leaving the "Locked" box with nothing in it (not filled in or checked). Go
> > back to your sheet and go to the protection option under Tools. Only check
> > the "Select Unlocked Cells" then hit ok and enter you password. Re-enter the
> > password and you are set. I hope this helps.
> >
> > "Andrew Stevens" wrote:
> >
> > > I have a protected Excel 2003 workbook with multiple protected worksheets.
> > > Protection is set so that users can only select and edit unprotected cells.
> > > The workbook is used by a number of people but it is not shared (each uses
> > > their own).
> > > Occasionally (I think unknowingly) someone will either cut and paste or drag
> > > and drop a cells or cells which fouls hidden formulae. It doesn't cause
> > > critical damage, but it's a nuisance when it happens. I was wondering if it
> > > was possible to
> > > a) disable cut and paste at the workbook or worksheet level; and
> > > b) disable drag and drop at the workbook or worksheet level (not the
> > > Application level)
> > > Any help would be greatly appreciated. Thanks