Disable cut, copy and paste in range of cell

[aashish83]

Hi All,

I have multiple sheets which already have certain macros running and want to ensure that a range of cells (for e.g. A33:B261) cannot be copied or cut ? i cannot use sheet protection as a method as that interrupts the functioning of existing macros...i am currently manging it with below code but this enables the data to be cut/copy and pasted in a new work book.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Locked = True Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub

 

[Jaafar Tribak]

How about using sheet protection but unprotect before running code and protect the sheet back when done.
You can also protect the sheet user interface only (Sheet1.Protect , , , , UserInterfaceOnly:=True) thus allowing code to edit the sheet with no restrictions

 

[aashish83]

How about using sheet protection but unprotect before running code and protect the sheet back when done.
You can also protect the sheet user interface only (Sheet1.Protect , , , , UserInterfaceOnly:=True) thus allowing code to edit the sheet with no restrictions

Where do i put this code?

 

[Jaafar Tribak]

In the ThisWorkbook module

Private Sub Workbook_Open()

    Dim oSh As Worksheet
   
    For Each oSh In Me.Worksheets
        oSh.Protect , , , , True
    Next oSh

End Sub

The above code will prevent users to edit the worksheets via the user interface but will allow code without restrictions.

 

[aashish83]

In the ThisWorkbook module

Private Sub Workbook_Open()

    Dim oSh As Worksheet
  
    For Each oSh In Me.Worksheets
        oSh.Protect , , , , True
    Next oSh

End Sub

The above code will prevent users to edit the worksheets via the user interface but will allow code without restrictions.

Hi i need the people to be able to edit some part of the worksheet (for e.g. A33:B261) needs to be restricted only for edit or cut copy?

 

[aashish83]

In the ThisWorkbook module

Private Sub Workbook_Open()

    Dim oSh As Worksheet
  
    For Each oSh In Me.Worksheets
        oSh.Protect , , , , True
    Next oSh

End Sub

The above code will prevent users to edit the worksheets via the user interface but will allow code without restrictions.

is it possible to mark a range to it? for e.g. C6:C19 and A33:A261 only? the rest can be editable?