How can I disable the use of "Cut/paste" from a protected cell, while still allowing a macro to use it?

[Lyryx]

I've put together a worksheet that is used by the nurses at our facility to record their hour and procedures.


After they fill out the sheet, they press a button tied to a macro that takes this data and adds it to a hidden table for the management team to review at later dates.


Everything in the workbook is running correctly and protected such that only 5 cells are allowed to be manipulated by the nursing team, however they can still use the cut paste function in these cells which could ruin the whole workbook.

TL;DR: I need to disable the use of Cut & paste from some cells. However I have a macro that will then need to access these cells which uses the Copy/Paste functions.

 

[Fishboy]

I've put together a worksheet that is used by the nurses at our facility to record their hour and procedures.


After they fill out the sheet, they press a button tied to a macro that takes this data and adds it to a hidden table for the management team to review at later dates.


Everything in the workbook is running correctly and protected such that only 5 cells are allowed to be manipulated by the nursing team, however they can still use the cut paste function in these cells which could ruin the whole workbook.

TL;DR: I need to disable the use of Cut & paste from some cells. However I have a macro that will then need to access these cells which uses the Copy/Paste functions.

Hi Lyryx,

Try this out in a COPY of your workbook...

- Press ALT+F11 to open the VBA Developer window
- Right-click on ThisWorkbook and select View Code
- In the window that opens simply copy / paste in the following 3 bits of code:

Private Sub Workbook_Activate()
'' When making this the active workbook - run this code


' Defines variable
Dim oCtrl As Office.CommandBarControl
    'Disable all Cut menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
            oCtrl.Enabled = False
     Next oCtrl
        'Disable all Copy menus
         For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
                oCtrl.Enabled = False
         Next oCtrl
            ' Prevent Drag and Drop
            Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
'' When making another workbook the active workbook - run this code


' Define variable
Dim oCtrl As Office.CommandBarControl
'Enable all Cut menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
            oCtrl.Enabled = True
     Next oCtrl
        'Enable all Copy menus
         For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
                oCtrl.Enabled = True
         Next oCtrl
            ' Re-enable Drag and Drop
            Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'' When selecting cells in this workbook - run this code


    ' Prevent Drag and Drop, clear any copied data from the clipboard
    With Application
        .CellDragAndDrop = False
        .CutCopyMode = False 'Clear clipboard
    End With
End Sub

The combination of macros above will prevent the user from copy / pasting / drag and dropping in your workbook. VBA should still be able to handle copying depending on how you format the copying code.

For example this seems to work...

Sheets("Sheet1").Range("A1").Copy Sheets("Sheet3").Range("A1")

But this doesn't...

Sheets("Sheet1").Range("A1").Copy
Sheets("Sheet3").Range("A1").Paste

Ultimately however you could potentially forgo the whole need for your macro to copy / paste with something like this...

Sheets("Sheet3").Range("A1").Value = Sheets("Sheet1").Range("A1").Value

 

[dubyarly]

I have a couple of similar workbooks, here is what I use:

Sub UnprotectSheets()
    Sheets("Sheet1").Unprotect "password"
    Sheets("Sheet2").Unprotect "password"
End Sub


Sub ProtectSheets()
    Sheets("Sheet1").Protect "password", AllowFiltering:=True
    Sheets("Sheet2").Protect "password", AllowFiltering:=True
End Sub

Just add the appropriate line at the beginning and end of your sub (I made them subs on their own because my books have several macros built-in which reference the same code).

Hope this helps.

 

[Lyryx]

This is definitely working nicely, which is great thank you.

I've reached this error in my Debug while trying to run my macro. Seems to be an issue with the paste special.
Seems like it's caused by the example you've mentioned earlier, but the code is too long to be kept to one line so it's falling down to the next.


here is the code I'm using

Sub PopulateProtect()'
' PopulateProtect Macro
'


'
    ActiveSheet.Unprotect
    Sheets("Table").Select
    ActiveSheet.Unprotect
    Sheets("Input Worksheet").Select
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("B3:B12").Select
    Selection.Copy
    Sheets("Table").Select
    Range("A2").Select
[B]    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/B]
[B]        :=False, Transpose:=True[/B]
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Input Worksheet").Select
    Range("B3:B11").Select
    Selection.ClearContents
    Range("B3").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

The bolded text is what the Debug is highlighting.

 

[Lyryx]

It is too late to edit, but Fishboy's code worked perfectly after closing out of excel and then reopening.

Not able to tell what changed, but it's working fine now. Thanks for the hand everyone.

 

[Fishboy]

It is too late to edit, but Fishboy's code worked perfectly after closing out of excel and then reopening.

Not able to tell what changed, but it's working fine now. Thanks for the hand everyone.

Happy to help