Excel copy/drag restrictions

  • Hi i have put together a excel VBA code that works in parts,

    Code
    1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    2. On Error Resume Next
    3. If ActiveSheet.ProtectContents = False Then
    4. Exit Sub
    5. End If
    6. Target.PasteSpecial xlPasteValues
    7. Application.CutCopyMode = True
    8. End Sub

    stops unprotected sheets from copy paste, so that it pastes values only, only problem i have with this one is that as soon as you click somewhere it pasts the values, I can live with that but would be nice if anyone also knew a fix for that.

    then i have this code:

    to stop drag and drop from being used and messing upp code in unprotected sheets.


    They work fine on their own, but as soon as i put them both into the workbook, the copy paste one is broken in the way that you can no longer copy past on to other sheets/workbooks. It only works on the same sheet.

    Anyone got a fix for that?


    I have these to protect condition formatings and codes from being messed upp by other users.

    But They need to be able to copy paste values inside the workbook and to other workbooks

  • In more detail, We have a very big dokument at work where we enter allot of data,

    I have put upp condition formatings that marks what we fill in red if they are impossible. such as runing hours on a machine going backwards or increasing by more than 24h/day.

    then we have allot of calculations and links from these numbers.


    Now draging or copy pasting whit out making it values only destroy our condition formatings and other formulas.


    There are allot of users using this document, so teaching them all to stop using copy paste or just dragging and dropping numbers from one spot to another when they have written in the wrong square, is impossible.

    Some of these numbers need to bee copied to other sheets and other excel documents every once in a while.


    so that is what i have been trying to fix by using the codes, but I am not very familiar with VBA coding, and trying to learn from looking at others codes, and was so happy when I got these 2 codes to work on their own, but as soon as put together, i lose that paste values only ability and option to copy datat out to another sheet/woorkbook.

  • So I have come to something that is much closer to what i need.

    I mad a macro in a module

    Code
    1. Sub PasteAsValues()
    2. On Error GoTo err_handler:
    3. If ActiveSheet.ProtectContents = False Then
    4. Selection.PasteSpecial Paste:=xlPasteAll
    5. Exit Sub
    6. End If
    7. Selection.PasteSpecial Paste:=xlPasteValues
    8. Exit Sub
    9. err_handler:
    10. End Sub

    and assigned that to Ctrl+v


    then i Have this code on the workbook:

    this enables and disables drag drop as i swap workbooks, but not when swaping sheet in the same workbook, alowing me to still use the copy paste in between sheets.


    These codes have 2 drawbacks, You are not able to use undo for copy paste, and im fine with that.


    second problem that is a bit mor annoying, the disabling of drag drop when swapping workbook clears the excel paste memory, so you can not paste anything in to another workbook.


    If anyone has any solution to the problem I would be grateful.

    But at this moment I am allot more happy where my workbook codes are at.

  • Forgot to mention


    I did try to find a Code that would make drag and drop values only, as this would most likely fix the problem of not being able to copy to other workbooks, but no luck yet on finding such a code.

    so if anyone has seen one of those codes in the internet jungle I would be interested in seeing it


    -Fresi :)