Disable Drag-Drop to Destination Cell; Don't Rename Named Range

[ryguy7272]

Typically, Excel users can easily click on a cell, then get the black cross
with four arrows, and then drag and drop to another cell, to change the value
of the destination cell, overwrite the contents of the cell, etc. Is there a
way to disable this behavior? I don’t mind users changing values of cells,
but I am trying to figure out a way to prevent users from changing a named
range (I have many named cells in my workbook). I’d love to be able to do
this for the active sheet, or for a sheet with a specific name, such as
‘Sheet1’. If this is possible, obviously, it has to be done with event code.

I’d greatly appreciate any recommendations or suggestions.

Thanks,
Ryan---

 

[Jim Cone]

Tools | Options | Edit (tab)... uncheck "Allow cell drag and drop"
--
Jim Cone
Portland, Oregon USA
(I didn't click anything)



"ryguy7272"
<[email protected]>
wrote in message
Typically, Excel users can easily click on a cell, then get the black cross
with four arrows, and then drag and drop to another cell, to change the value
of the destination cell, overwrite the contents of the cell, etc. Is there a
way to disable this behavior? I don’t mind users changing values of cells,
but I am trying to figure out a way to prevent users from changing a named
range (I have many named cells in my workbook). I’d love to be able to do
this for the active sheet, or for a sheet with a specific name, such as
‘Sheet1’. If this is possible, obviously, it has to be done with event code.
I’d greatly appreciate any recommendations or suggestions.
Thanks,
Ryan---

 

[ryguy7272]

Thanks Jim! That will disable the drag and drop functionality totally. I
was hoping for a solution that would allow someone to drag and drop, but
leave a named range in its original spot; the named range is copied and
pasted from the original source to the new destination. I was hoping to find
a way to just take the value, but not the named range that is attached to the
cell. I guess there is not a way to do this. Is that right?

Thanks,
Ryan---

 

[Jim Cone]

You could enable or disable DragAndDrop on a particular sheet
by using the Sheet_Activate/Sheet_Deactivate events.
It is very difficult to prevent copying and pasting.
You may need to place all of your data on a separate sheet and
protect it or hide the sheet.

Also, it is not clear if you are doing the copying and pasting or a user is.
You can, course, just copy data from a named range and paste values.
--
Jim Cone
Portland, Oregon USA



"ryguy7272"
<[email protected]>
wrote in message
Thanks Jim! That will disable the drag and drop functionality totally. I
was hoping for a solution that would allow someone to drag and drop, but
leave a named range in its original spot; the named range is copied and
pasted from the original source to the new destination. I was hoping to find
a way to just take the value, but not the named range that is attached to the
cell. I guess there is not a way to do this. Is that right?

Thanks,
Ryan---

 

[RyGuy]

The user is copying/pasting. I was trying to think of a way to prevent
accidental changes of named ranges. That’s the objective. I would like to
force a kind of copy-paste-special-values scenario on a particular sheet. Is
there a way to ONLY prevent changes of named ranges or accidentally copying
over an existing named range with new named range?

Thanks,
Ryan---

 

[Jim Cone]

Make a copy of the sheet. Hide it.
Use the sheet change event to compare changed cells against the hidden one.
If not the same, use Application.Undo and Msgbox "Don't do that"
--
Jim Cone
Portland, Oregon USA



"RyGuy"
<[email protected]>
wrote in message
The user is copying/pasting. I was trying to think of a way to prevent
accidental changes of named ranges. That’s the objective. I would like to
force a kind of copy-paste-special-values scenario on a particular sheet. Is
there a way to ONLY prevent changes of named ranges or accidentally copying
over an existing named range with new named range?

Thanks,
Ryan---

 

[ryguy7272]

THANKS!!

 

[Jim Cone]

You are welcome. Glad it helped.
'--
Jim Cone



"ryguy7272"
<[email protected]>
wrote in message
THANKS!!