Drag and drop cell values only

[Edjz]

Hi, I am working on a workbook that is used to plan and track the many different activities of a day at my place of work. It has lots of conditional formatting and validation that needs to stay in the same place but values get moved around a lot as staff swap jobs, go sick, finish their tasks etc. Additionally, the person keeping track of things changes sometimes multiple times during the day and will not necessarily have more than a very basic understanding of excel.

What I'd love is an option when protecting a worksheet to allow edits to values only but in lieu, I need to restrict the damage that can be done. I have a macro to bind copy to ctrl+x and paste values only to ctrl+v so that's taken care of. I know I can disable drag and drop and right click pasting but can anyone tell me if there is a VBA sub I can write to:

a) Have drag and dropping cells by the border move values only or ideally;
b) Have dragging and dropping swap the target values with the destination values as 9/10 times that's what the aim will be and there aren't really any issues for what we're doing with accidentally swapping values where as overwring can be a problem?

I appreciate there are many ways to achieve pasting values only using paste special, right click +v is great for me but I am just not gonna be able to get everyone who might use this spreadsheet to be careful so it needs to be foolproof.

Thanks!

 

[severynm]

Welcome!

For problem 2, instead of click and dragging, hold Shift and then drag to swap cell positions. That helps with swapping cell locations easily, but like cut/copy will also move conditional formatting with the cells. I don't know if that is something you can automatically force though.

For problem 1, what you might be able to do is put together a small VBA script that takes the range the user has selected, reads it to an array, takes a second range to swap places with, and then does the swap. This is one solution already that does that. It reads the different areas the user has previously selected with ctrl+select.