Allow paste in worksheet without overwriting locked cells

Question

I have a protected worksheet that users would like to copy and paste into. I have no control over the workbook they are copying from.

The protected worksheet has some rows that are available for data entry, and other rows that are locked and greyed out to the user. The users would like to be able to paste over the top of the entire worksheet from another random workbook and have all the cells available for data entry filled in, while the locked cells are undisturbed. In the current state, the user gets an error when they try to paste, because it cannot paste over the locked cells.

Example:
Worksheet 1:

Act1 100 100 100
Act2 100 100 100
Act3 100 100 100

Worksheet 2: (The second row is locked)

Act1 300 300 300
Act2 200 200 200
Act3 100 100 100

After copying/pasting Worksheet 2 should look like this:

Act1 100 100 100
Act2 200 200 200
Act3 100 100 100

The values from worksheet 1 are populated and the locked rows are undisturbed.

• I've been thinking along the lines of having a hook where on paste, the locked cells are unlocked so that the paste can happen, and then are reverted to their original values and relocked.
• Is there some way I can loop through the cells in the clipboard and only paste cells where the target isn't locked?
• It is preferable to not create a separate button for paste, so there is less impact on the users, but if that's the only way, I'm not opposed to it.
• Currently, I plan on grouping the locked rows together, so that the data entry cells are contiguous, but then the accounts will be out of order, which is not preferred.

Answer 1

Requirements:

1. Allow pasting into protected sheets
2. Retain content in the locked cells after paste operation
3. Retain protection status of the sheet

Method:

1. Handle all possible paste operations in user defined module, instead of Excel's way
2. Since unprotecting removes contents from clipboard paste to a temp sheet
3. make a note of user intended paste location
4. make a note of locked cells in the protected sheet (content and address)
5. unprotect the sheet
6. paste to intended cells from temp sheet
7. remove temp sheet and protect main sheet

I referred to Jan Karel's Catch Paste sample for reference. You might want to add all the ways he is catching paste operations.

In the ThisWorkbook module add below code

Private mdNextTimeCatchPaste As Double

Private Sub Workbook_Activate()
    REM Add Paste event handler
    CatchPaste
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    REM Restore Paste event handler
    StopCatchPaste
    mdNextTimeCatchPaste = Now
    Application.OnTime mdNextTimeCatchPaste, "'" & ThisWorkbook.Name & "'!UnProtectPasteToSheet"
End Sub


Private Sub Workbook_Deactivate()
    REM Restore Paste event handler
    StopCatchPaste
    On Error Resume Next
    REM Cancel scheduled macroREM s,
    REM because we might be closing the file
    Application.OnTime mdNextTimeCatchPaste, "'" & ThisWorkbook.Name & "'!UnProtectPasteToSheet", , False
End Sub

Private Sub Workbook_Open()
    REM Add Paste event handler
    CatchPaste
End Sub

Add a new Module and add below code

REM Add Paste event handler
Public Sub CatchPaste()
REM these are the ways you can Paste in to Excel
REM refer to http://www.jkp-ads.com/articles/catchpaste.asp for more details
Application.OnKey "^v", "UnProtectPasteToSheet"
Application.OnKey "^{Insert}", "UnProtectPasteToSheet"
Application.OnKey "+{Insert}", "UnProtectPasteToSheet"
Application.OnKey "~", "UnProtectPasteToSheet"
Application.OnKey "{Enter}", "UnProtectPasteToSheet"
End Sub
REM restore all default events
Public Sub StopCatchPaste()
Application.OnKey "^v", ""
Application.OnKey "^{Insert}", ""
Application.OnKey "+{Insert}", ""
Application.OnKey "~", ""
Application.OnKey "{Enter}", ""
End Sub

REM Here we will check the sheet is protected, if it is then paste to a temp sheet,
REM unprotect main sheet, paste the values, and restore locked cells
Private Sub UnProtectPasteToSheet()
On Error GoTo ErrHandler
Dim bProtected As Boolean, oSheet As Worksheet, oTempSheet As Worksheet, sPasteLocation As String
Dim oCell As Range, oCollAddress As New Collection, oCollValue As New Collection, iCount As Integer

REM check protection status
If Not ThisWorkbook.ActiveSheet.ProtectContents Then
    Selection.PasteSpecial Paste:=xlAll
Else
    bProtected = True
    Set oSheet = ThisWorkbook.ActiveSheet
    REM save paste location
    sPasteLocation = Selection.Address
    REM unprotecting clears Clipboard in Excel!! strange but true..
    REM So paste it to a new sheet before unprotecting
    Set oTempSheet = ThisWorkbook.Worksheets.Add
    REM oSheet.Visible = xlSheetVeryHidden
    oTempSheet.Paste
    REM unprotect the sheet
    oSheet.Unprotect

    REM make a note of all locked cells
    For Each oCell In oSheet.UsedRange
        If oCell.Locked Then
            oCollAddress.Add oCell.Address
            oCollValue.Add oCell.Value
        End If
    Next

    REM paste
    oTempSheet.UsedRange.Copy
    oSheet.Activate
    oSheet.Range(sPasteLocation).Select
    REM you need to paste only values since pasting format will lock all those cells
    REM since in Excel default status is "Locked"
    Selection.PasteSpecial xlValues

    REM remove temp sheet
    Application.DisplayAlerts = False
    oTempSheet.Delete
    Application.DisplayAlerts = True

    REM restore locked cells
    For iCount = 1 To oCollAddress.Count
        Range(oCollAddress.Item(iCount)) = oCollValue.Item(iCount)
    Next
    REM restore protection
    oSheet.Protect

End If
Exit Sub

ErrHandler:
    Debug.Print Err.Description
    If bProtected Then
        ThisWorkbook.ActiveSheet.Protect
    End If
End Sub

Note: I am adding REM instead of ' to keep the Stackoverflow formatter happy. Give it a try and let me know how it goes..

Answer 2

Having dealt with many of the cutting and pasting issues, I can say that the simple solution to the problem is to create a button that will do the entire copy. This will only work (easily) if they're always copying from the same workbook (though you could program a more complicated interface if you needed to).

The code can survey the locked cells, then selectively break up the copied cells into contiguous ranges, and paste each individual range.

Answer 3

You can actually abort the paste operation if you detect the Paste area overlaps with the locked cells. In fact Office-2007 does this for you, if any of the the cells being pasted are locked and the sheet is protected then Office-2007 fails the Paste operation wnd throws an error message.

In previous versions of the Excel and in un-protected sheets (but with few locked cells, which does not serve any purpose) you can have a function to undo the changes if any of the cells being modified is locked.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
    If oCell.Locked = True Then
        'disable events to prevent recursive function call
       Application.EnableEvents = False
       'undo the paste
       Application.Undo
       'enable events
       Application.EnableEvents = True
       Exit For
    End If
Next
End Sub

Edit: After posting that answer I realised that in Excel all the calls are marked as Locked by default. So if they paste from an ordinary sheet, then chances are the destination cell will read "Locked" because the past just locked it!! So I have an improved way, which will allow you to paste some thing to a sheet, it will just keep the "Locked" cells intact.

The idea here is we will capture the new status after the paste, and then undo all the changes. Then we will loop through the cells that were just changed and check if they were locked before Paste operation. If they were not, then we will repopulate the pasted value. Using this code you will get the results you were asking in your example.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range, oCollAddress As New Collection, oCollValue As New Collection, iCount As Integer
'get all pasted content in to a collection
For Each oCell In Target
    oCollAddress.Add oCell.Address
    oCollValue.Add oCell.Value
Next

'undo the changes done, and re-paste it for unlocked cells
'disable events to prevent infinite calls
Application.EnableEvents = False
Application.Undo
For iCount = 1 To oCollAddress.Count
    If Range(oCollAddress.Item(iCount)).Locked = False Then
        Range(oCollAddress.Item(iCount)) = oCollValue.Item(iCount)
    End If
Next
Application.EnableEvents = True
End Sub

Edit 5/27/2010: Okay, then you need to capture Paste operation (event), and handle it manually instead of Excel. I am adding a new answer since that is too big..

Answer 4

I think the key is to gracefully block standard Paste function and redo the Paste in a controlled way

I heared that in later Excel versions there is a "On-Paste" event (not sure), but this is not available in 2003. I trap Paste actions by the following code in 2003 (which is called by a suitable event procedure like Sheet_Activate() ):

Sub SetPasteTrap(Mode As Boolean)
' TRUE sets the trap, FALSE releases trap
    If Mode Then
        Application.CommandBars("Edit").Controls("Paste").OnAction = "TrappedPaste"
        Application.CommandBars("Edit").Controls("Paste Special...").OnAction = "TrappedPaste"
        Application.CommandBars("Cell").Controls("Paste").OnAction = "TrappedPaste"
        Application.CommandBars("Cell").Controls("Paste Special...").OnAction = "TrappedPaste"
        Application.OnKey "^v", "TrappedPaste"
    Else
        Application.CommandBars("Edit").Controls("Paste").OnAction = ""
        Application.CommandBars("Edit").Controls("Paste Special...").OnAction = ""
        Application.CommandBars("Cell").Controls("Paste").OnAction = ""
        Application.CommandBars("Cell").Controls("Paste Special...").OnAction = ""
        Application.OnKey "^v"
    End If
End Sub

By this we trap main menu, context menu and the Ctrl-V key - that should be enough. The OnAction property diverts to the sub contained in the argument

Sub TrappedPaste()
    If ActiveSheet.ProtectContents Then
        ' as long as sheet is protected, we don't paste at all
        MsgBox "Sheet is protected, all Paste/PasteSpecial functions are disabled." & vbCrLf & _
               "At your own risk you may unprotect the sheet." & vbCrLf & vbCrLf & _
               "When unprotected, you can copy/paste from other text, WORD, HTML or EXCEL files." & vbCrLf & _
               "All Paste operations will implicitly be executed as PasteSpecial/Values", _
               vbOKOnly, "Paste"
        Exit Sub
    End If

    ' silently do a PasteSpecial/Values
    On Error GoTo TryExcel
    ' try to paste text
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    Exit Sub
TryExcel:
    On Error GoTo DoesntWork
    Selection.PasteSpecial xlPasteValues
    Exit Sub
DoesntWork:
    MsgBox "Sorry - wrong format for pasting", vbExclamation + vbOKOnly, "PasteSpecial ..."
End Sub

I am adding this because it shows that you must care a bit what is in the buffer (excel, text, html, etc.)

You would need to substitute the core of the TrappedPaste() routine by a code that

1) pastes the content into a hidden sheet/range (you can use the code above)

2) unprotects the target sheet

3) moves the content to the target range cell by cell on the condition that

4) the target cell fulfills the condition of not having a lock, validation or similar

5) re-protects the target sheet

6) empty the hidden sheet/range

Note that with such a construct the user will not be able to use the UNDO function!

Hope that helps - Good Luck MikeD