VBA to Disable edit, Copy and Cut for Specific Range...without using sheet protection

[aashish83]

Hi i have multiple sheets with important data and wish for certain range in each sheet should not be editable or should nut be copied or cut and pasted else where. I have tried doing it using sheet protection but ran into issues as my sheet has automated formulas and many macros that require filter and sorting too. is there any way out?


for e.g.

I have a sheet named Level A where i don't want C6:C19 to be editable or can be copied and then on the same sheet A34:B261 to be the same and the rest of the sheet can be editable.

 

[DanteAmor]

Check if the following option works for you.
Put the following code in the events on the sheet where you want it to work.
With the following code they will not be able to select the cells and therefore will not be able to edit or copy them.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("C6:C19, A34:B261")) Is Nothing Then
    On Error GoTo selectA1
    Target.Offset(, 1).Select
  End If
  Exit Sub
selectA1:
  Range("A1").Select
End Sub

 

[aashish83]

You are a super star Dante!!! worked great!!!

 

[Siddharth Rout]

The above solution will not work if you copy say D1:D10 and paste it in C1. The data in C6 will get overwritten.

Try this as well please. I have not tested this completely. Do let me know if you find any bug?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False
    If Not Intersect(Target, Range("C6:C19, A34:B261")) Is Nothing Then Application.Undo

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

 

[aashish83]

The above solution will not work if you copy say D1:D10 and paste it in C1. The data in C6 will get overwritten.

Try this as well please. I have not tested this completely. Do let me know if you find any bug?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False
    If Not Intersect(Target, Range("C6:C19, A34:B261")) Is Nothing Then Application.Undo

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Thanks for pointing it out Siddharth didn't really think about this possibility till now...will give it a try and revert shortly!

 

[aashish83]

The above solution will not work if you copy say D1:D10 and paste it in C1. The data in C6 will get overwritten.

Try this as well please. I have not tested this completely. Do let me know if you find any bug?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False
    If Not Intersect(Target, Range("C6:C19, A34:B261")) Is Nothing Then Application.Undo

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Just tested the original code by Dante with paste in the range and it did not let me paste it as the selection of cell is blocked by the code and if try and select from above the range it still doesn't paste it in the range!

 

[Siddharth Rout]

Just tested the original code by Dante with paste in the range and it did not let me paste it as the selection of cell is blocked by the code and if try and select from above the range it still doesn't paste it in the range!

It will not let you paste in the range but if you paste outside just like I mentioned then it will not stop you from pasting. Copy say D1:D10 and paste it in C1. The data in C6 will get overwritten.