VBA: Prevent CopyCutPaste and DragAndDrop on a given sheet

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
I want to prevent certain actions on a sheet called "HOME":
1. User should not be able to copy or cut from the sheet - that's either of the techniques. I have disabled right clicking so that part is out for now. But keyboard shortcuts are still alive.

2. User should not be able to paste data to the sheet. That's user should not be able to drag to fill or copy cells.

Can someone please help?

Thanks in advance
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Using Application.OnKey you can redefine a hotkey. Application.CellDragAndDrop disables/enables that action.
Substitute Sheet1.Test with a blank procedure, so nothing happens, if that's what you want. To re-enable Ctrl+C you need to run the sub again, but this time removing the 2nd argument.
To make this only happen on a certain sheet, go to that sheet's module, choose SheetActivate and SheetDeactivate. Put the code from subbs into SheetActivate, and the code from dubbs into SheetDeactivate. Hope that helps.

VBA Code:
' Assuming the following is on a sheet named HOME
Sub Test()

    MsgBox "Hello"

End Sub

Private Sub Worksheet_Activate()

    Application.OnKey "^{c}", "Home.Test"
    Application.OnKey "^{v}", "Home.Test"
    Application.CellDragAndDrop = False

End Sub

Private Sub Worksheet_Deactivate()

    Application.OnKey "^{c}"
    Application.OnKey "^{v}"
    Application.CellDragAndDrop = True

End Sub
 
Last edited:
Upvote 0
Solution
Okay it works fine.

What does this part mean?
"Sheet1.Test"

And when I press ctrl + c, can I want to avoid that alert that shows up maybe with my own alert. Is that posible?
 
Upvote 0
Okay it works fine.

What does this part mean?
"Sheet1.Test"

And when I press ctrl + c, can I want to avoid that alert that shows up maybe with my own alert. Is that posible?
Oops. I think I misunderstood your explanation.

I used the blank procedure and it works.

But it seems to apply to all sheets.

How do I make it apply to just the HOME sheet?
 
Upvote 0
Oops. I think I misunderstood your explanation.

I used the blank procedure and it works.

But it seems to apply to all sheets.

How do I make it apply to just the HOME sheet?
Did you put the code in the Home module's Activate & Deactivate events? It's not 100% foolproof, but it will do the job in most situations. When you visit Home tab, the code activates, and when you leave it deactivates. I edited my original post, so you may have missed that part of my explanation.
 
Upvote 0
Okay it's working now.
What I did was to call the subbs in the workbook open event.
Then place the above codes as you directed and it's doing the job.

Thanks again. Have a great day.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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
Back
Top