Drag and drop cell values only

Edjz

New Member
Joined
Jul 2, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,618
Messages
5,707,427
Members
421,509
Latest member
someinternetuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top