I can't copy data from one workbook to another due to Application.CellDragAndDrop = False

[mcomp72]

I have a workbook where I want to limit the user's ability to accidentally change the formatting on certain unprotected cells. (The cell can't be protected because I need to allow the user to input data into them, I just don't want things to get changed like number formatting, background color, etc.)

One of the things I'm doing to make sure they don't accidentally change the formatting is I have this line of the code in the Workbook_Activate sub:

Application.CellDragAndDrop = False

The problem I'm having is, this line of code seems to remove any data from the clipboard. So if the user is trying to copy data from a different workbook, it won't work. Whenever I hit paste, nothing happens except for the "ding" sound that Excel makes.

Is there any way to set CellDragAndDrop to False without having it remove whatever is in the clipboard? If not, any suggestions for how I can disable CellDragAndDrop in my workbook but still allow the user to copy & paste from a different workbook?

 

[DataBlake]

I have a workbook where I want to limit the user's ability to accidentally change the formatting on certain unprotected cells. (The cell can't be protected because I need to allow the user to input data into them

There are quite a few add-ons that have the ability to lock cell formatting.
I know Kutools has one in the Worksheet Design tab
may want to research some add-ons for simple design elements such as that

 

[mcomp72]

Thanks, Blake. I will look into Kutools. In regards to CellDragAndDrop, I'm guessing there is no way to stop it from deleting whatever is in the clipboard when you set it to False?

 

[DataBlake]

Thanks, Blake. I will look into Kutools. In regards to CellDragAndDrop, I'm guessing there is no way to stop it from deleting whatever is in the clipboard when you set it to False?

not that i am aware of, but perhaps someone else could shed some light on this?