• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cut,Copy,Paste disable

Abhijeet

Active Member
Hi

I want cut,copy,Paste disable for particular range in excel sheets.Please tell me how to do this
Code:
Sub ToggleCutCopyAndPaste(Allow As Boolean)
    'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
   
    'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
   
    'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^c", "CutCopyPasteDisabled"
            .OnKey "^v", "CutCopyPasteDisabled"
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
    'Activate/Deactivate specific menu item
    Dim cBar As CommandBar
    Dim cBarCtrl As CommandBarControl
    For Each cBar In Application.CommandBars
   
    If cBar.Name <> "Clipboard" Then
            Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
            If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
        End If
       
    Next
End Sub
Sub CutCopyPasteDisabled()
    'Inform user that the functions have been disabled
    MsgBox "Sorry!  Cutting, copying and pasting have been disabled in this workbook!"
End Sub

'*** In the ThisWorkbook Module ***
Option Explicit
Private Sub Workbook_Activate()
    Call ToggleCutCopyAndPaste(False)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Deactivate()
    Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Open()
    Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'Toggle the cut, copy & paste commands on selected ranges
   
    Select Case Sh.Name
    Case Is = "Sheet1"
        'Disable cut, copy & paste for Sheet1, Column B
        If Not Intersect(Target, Target.Parent.Columns(2)) Is Nothing Then
            Call ToggleCutCopyAndPaste(False)
        Else
            Call ToggleCutCopyAndPaste(True)
        End If
       
    Case Is = "Sheet2"
        'Disable cut, copy & paste for Sheet2, Column B
        If Not Intersect(Target, Target.Parent.Columns(2)) Is Nothing Then
            Call ToggleCutCopyAndPaste(False)
        Else
            Call ToggleCutCopyAndPaste(True)
        End If
       
    Case Is = "Sheet3"
        'Disable cut, copy & paste for Sheet2, Column B
        If Not Intersect(Target, Target.Parent.Columns(2)) Is Nothing Then
            Call ToggleCutCopyAndPaste(False)
        Else
            Call ToggleCutCopyAndPaste(True)
        End If
       
    Case Else
        'Re-enable cut copy and paste commands as this is not a restricted sheet
        Call ToggleCutCopyAndPaste(True)
    End Select
End Sub
 
But i want disable for entire workbook because i have 30 sheets in workbook so please tell me how to do this
 
I tried this this disable cut copy but paste is not if i copy from different file then then just hit enter then that data is paste in that range i want avoid paste also then please tell me
 

Attachments

  • Data validation.xlsm
    19.6 KB · Views: 9
This work but pls tell me particular range i want cut,copy,paste disable not entire sheet i want disable. please tell me
 
... Then you want to modify link 1 code.

The portion in ThisWorkbook SheetSelectionChange event to something like below.
Adjust Range as needed.
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'Toggle the cut, copy & paste commands on selected ranges
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If Not Intersect(Target, Target.Parent.Range("A1:A20")) Is Nothing Then
            Call ToggleCutCopyAndPaste(False)
        Else
            Call ToggleCutCopyAndPaste(True)
        End If
    Next ws
End Sub
 

Attachments

  • Data validation2.xlsm
    22.8 KB · Views: 22
Hi Chihiro

If i copy from Cell I2 & hit enter in A1 cell then paste that data.
If i use Ctrl +V then that is disable but hit enter then it is not disable please tell me how to disable in Given Range
 
Try this.
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    'Toggle the cut, copy & paste commands on selected ranges
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If Not Intersect(Target, Target.Parent.Range("A1:A20")) Is Nothing Then
            Application.CutCopyMode = False
            Call ToggleCutCopyAndPaste(False)
            Application.CutCopyMode = True
        Else
            Call ToggleCutCopyAndPaste(True)
        End If
    Next ws
End Sub
 

Attachments

  • Data validation2 (1).xlsm
    22.9 KB · Views: 90
Back
Top