Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Prevent copy and paste in one worksheet (Excel 2002/2003)

    Hi

    Is there a way to disable cut and paste in just a specific worksheet.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevent copy and paste in one worksheet (Excel 2002/2003)

    Do you want the user to be able to
    - enter new data
    - edit existing data
    - delete data
    - format cells

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Prevent copy and paste in one worksheet (Excel 2002/2003)

    Hi Hans

    Thanks for the prompt reply

    I just want them to enter data and edit data.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevent copy and paste in one worksheet (Excel 2002/2003)

    See the attached workbook. There is code in
    - Module1
    - The Sheet1 module
    - The ThisWorkbook module
    I hope it covers most situations.

    Notes:
    - Clever users will be able to find ways around it.
    - If the user disables macros, it won't work at all.

    With thanks to Mr Excel for most of the code.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Prevent copy and paste in one worksheet (Excel 2002/2003)

    Hi Hans

    When I open your example I get a runtime error Method DisplayClipboardWindow of Object Application failed.

    Public Sub DisableCopy()
    Application.CutCopyMode = False
    Application.OnKey "^c", "Dummy"
    Application.OnKey "^v", "Dummy"
    Application.OnKey "^x", "Dummy"
    EnableControl 21, False ' cut
    EnableControl 19, False ' copy
    EnableControl 22, False ' paste
    EnableControl 755, False ' pastespecial
    EnableControl 809, False ' office clipboard
    Application.DisplayClipboardWindow = False <<< error here
    Application.CellDragAndDrop = False
    End Sub

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Prevent copy and paste in one worksheet (Excel 2002/2003)

    It works for me in Excel 2003, but if it doesn't for you, just comment out the line.

    Without this line, the user will be able to paste items from the Office Clipboard task pane.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Prevent copy and paste in one worksheet (Excel 2002/2003)

    Hi Hans

    I am using office 2002 here, I will try it in 2003 when I get home.

    Thank you for you for your efforts.

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Prevent copy and paste in one worksheet (Excel 2002/2003)

    Hans' spreadsheet appears to have been lost in the Lounge crash some time back. FWIW, very similar code can be found here.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •