Drag & Drop Corupts protected cells

[DennisK]

Why is it that when you protect a worksheet so that formulas
CANNOT be changed, which works great, until a person drags and drops a cell
that is NOT protected because info needs to be typed in that cell, The drag
and drop corrupts the formulas in the cells where the person dragged and
dropped the info. I know you can uncheck the drag and drop box under
Tools/Options, but that makes the change to any spreadsheets you open. You
cannot save that tools/option just to the worksheet you have open.

 

[AKphidelt]

Alright, not sure how familiar you are with VBA... so Ill give you a detailed
instruction. Go to the VBE (Visual Basic Editor)

In the Project Explorer to the left, select the workbook that has the
worksheet... then double click on the worksheet that you want protected.

A module should open

On the drop down list on the top left of the Module that says (General)...
select "Worksheet"

Some private sub function should pop up. Delete that and insert this sub.

Private Sub Worksheet_Activate()

Application.CellDragAndDrop = False

End Sub

Then Below this enter this function

Private Sub Worksheet_Deactivate()

Application.CellDragAndDrop = True

End Sub

Make sure you copy and paste this word for word... there should be nothing
else on the module... and make sure you double click on the worksheet and not
just insert a new module... and make sure you select worksheet. What this
does is anytime someone opens that worksheet Cell Drag and Drop is turned
off... whenever someone leaves that worksheet Cell Drag and Drop is turned
on. Let me know if you have any other quesitons.

 

[DennisK]

Thanks AKphidelt for the info, I must be doing something wrong, just started
learning about VBA. Applied macro just like you said, When I open the .xls
file I say enable macros, but I can still drag & drop on worksheet. I have
30 worksheets in the .xls file. Do I need to apply the macro to each one?
Macro Security is set to med. Does it matter if worksheet is protected when
I apply macro?
Thanks for help, DennisK

 

[Susan]

Dennis - yes, you would have to copy this macro to every single
worksheet. perhaps we could change it to an auto_open & before_close
event code? that would affect the whole workbook, i think.

put these in the WORKBOOK module (not the worksheets - is @ the end of
list of worksheets in the VBE editor).

sub auto_open()
Application.CellDragAndDrop = False
end sub

sub before_close
Application.CellDragAndDrop = true
end sub

not 100% sure - they might need the word "public" or "private" before
the word "sub".
if i'm making a horrible mistake somehow in suggesting this, i hope
AKphidelt will let us know.............

susan

 

[Susan]

this is the correct coding.
it goes in the This Workbook module, as i said before.

for testing purposes, there are two msgboxes that pop up. obviously
you won't want your users to know you've turned this off! so when
you're done testing, add an apostrophe '
before the words "msgbox"
then it won't run anymore.

susan
=======================
Option Explicit

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
MsgBox "drag & drop is OFF"
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
MsgBox "drag & drop is ON"
End Sub
===============================

 

[AKphidelt]

You should be able to run it on protected sheets at the medium security
level. I didn't realize there were 30 sheets that you need this on. Your best
bet would be to set that entire workbook to protect. However, there is a way
when the workbook is initialized to select those certain worksheets you want
to protect, and protect them. Although, I don't know exactly how it's done, I
will look in to it. Let me know if you have any other questions.

 

[DennisK]

Thanks Susan, It worked, fantastic. Liked the msgbox part too. Thanks Again


DennisK

 

[DennisK]

Thanks AKphidelt, I Did get the single sheet to work and then changed it to
do the entire workbook. I use an add-in called ASAP Utilites to protect the
whole workbook, it lets you protect all the worksheets at once and unprotect
them all at once. I guess there is a way using VBA to do it also. I have 63
worksheets to the workbook, 30 that needed drag and drop removed. Thanks
Again for your help
DennisK