I have a workbook that some very non-technical people will be entering data into. In the past, I've had them type over formulas and otherwise screw up the data, so I've locked everything except where they're supposed to enter data, then I've password protected the workbook and worksheet. Unfortunately, Microsoft, in their infinite wisdom, decided that paste was a function that should be disabled in a protected worksheet/workbook (at least in Excel 2010).

Now, I've discovered that someone's been keeping data in their own workbook and needs to transfer it to mine, since mine is the official one. I borrowed this code from the internet, and have tweaked it a bit to more accurately apply to my particular situation. The code works just fine, however, it's quite rather slow, and I believe that it's the two loops that are killing performance.

I know there are a lot of other things that can be tweaked (Hungarian notation FTL), but I'm not concerned about those at the moment, I need to make this work faster and get it to the end user so she can copy/paste about 200 rows of data (in 3 groups of columns, due to the structure of the data) ASAP. I'll clean up the other little things with the help of RubberDuck once I've got it out the door. (Yes, I understand that's the wrong way about it, but time is of the essence.)

Here's the code:

Private Sub UnProtectPasteToSheet()

  On Error GoTo ErrHandler
  Dim bProtected As Boolean
  Dim oSheet  As Worksheet
  Dim oTempSheet As Worksheet
  Dim sPasteLocation As String
  Dim oCell As Range
  Dim oCollAddress As New Collection
  Dim oCollValue As New Collection
  Dim iCount  As Integer

  ' check protection status
  If Not ThisWorkbook.ActiveSheet.ProtectContents Then
    Selection.PasteSpecial Paste:=xlAll
    Set oSheet = ThisWorkbook.ActiveSheet
    'first unprotect the workbook so we can add the worksheet
    Dim ProtectStructure As Boolean
    Dim ProtectWindows As Boolean
    ToggleWorkbookProtection TheBook:=ThisWorkbook, Structure:=ProtectStructure, Window:=ProtectWindows, ProtectTheBook:=False

    ' save paste location
    sPasteLocation = Selection.Address
    ' unprotecting clears Clipboard in Excel!! strange but true..
    ' So paste it to a new sheet before unprotecting
    Set oTempSheet = ThisWorkbook.Worksheets.Add
    ' oSheet.Visible = xlSheetVeryHidden
    'then unprotect the sheet after storing off the pasted data
    oSheet.Unprotect Password:=MY_PWD
    bProtected = True

    ' 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

    ' paste
    ' you need to paste only values since pasting format will lock all those cells
    ' since in Excel default status is "Locked"
    Selection.PasteSpecial xlValues

    ' remove temp sheet
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

    ' restore locked cells
    For iCount = 1 To oCollAddress.Count
      Range(oCollAddress.Item(iCount)) = oCollValue.Item(iCount)
    ' restore protection
    oSheet.Protect Password:=MY_PWD
    If ProtectStructure Or ProtectWindows Then
      ToggleWorkbookProtection TheBook:=ThisWorkbook, Structure:=ProtectStructure, Window:=ProtectWindows, ProtectTheBook:=True
    End If

  End If
  Exit Sub

  Debug.Print Err.Description
  If bProtected Then
  End If

End Sub

Private Sub ToggleWorkbookProtection(ByRef TheBook As Workbook, ByRef Structure As Boolean, ByRef Window As Boolean, ByVal ProtectTheBook As Boolean)

  If ProtectTheBook Then
    TheBook.Protect Structure:=Structure, Windows:=Window, Password:=MY_PWD
    Structure = TheBook.ProtectStructure
    Window = TheBook.ProtectWindows
    If Structure Or Window Then                   'only attempt to unprotect if it's protected.
      TheBook.Protect Structure:=False, Windows:=False, Password:=MY_PWD
    End If
  End If

End Sub
  • \$\begingroup\$ Without reviewing your code, have you tried keeping the worksheet unprotected but capturing the Paste event? If you can catch the data pasted by the user, you could use the pasted value and insert it into whatever formula or format that exists in the sheet -- keeping it completely intact as you intend. See Excel VBA How to detect if something was pasted in a Worksheet \$\endgroup\$ – PeterT Oct 14 '16 at 15:21
  • \$\begingroup\$ @PeterT I have not. Until now, protecting it was sufficient. I'll take a look at that link and see if that looks like it may be a more effective method. \$\endgroup\$ – FreeMan Oct 14 '16 at 15:24
  • \$\begingroup\$ You should be able to allow pasting in a protected sheet, too \$\endgroup\$ – Raystafarian Oct 17 '16 at 20:11
  • \$\begingroup\$ @Raystafarian - that may work in Excel 2013, but it does not work in 2010. (Sorry, just added the 2010 version to the OP - I'd left it out initially.) Additionally, I already have a selected set of cells unlocked, that's how the users are able to enter data into the worksheet. There are several columns with formulas that are locked that they're not allowed to enter anything into. \$\endgroup\$ – FreeMan Oct 17 '16 at 20:23

To catch the Paste event, the description in Excel VBA How to detect if something was pasted in a Worksheet provides good information. But to fully implement it, there are a couple of steps you need to watch for.

  1. The link describes how to catch and recognize a Paste event, but then you need to perform the Undo as well and correctly recover the original formula
  2. When you Replace the existing formula value with the newly pasted value, this triggers another Worksheet_Change event that you have to overcome.

To illustrate how to work around this, in Cell C3 enter the following formula:

="You pasted a "&TEXT(17.88,"0.000")

Then, in the Sheet module for your worksheet, try this example:

Option Explicit

Private workingCell As String

Private Sub Worksheet_Change(ByVal Target As Range)

    '--- restricts checking for a paste event to a
    '    specific range
    If (Target.Address = "$C$3") And (Len(workingCell) = 0) Then
        '--- set the workingCell to the target address
        '    when the "replace" occurs below, it triggers
        '    another change event for the same cell
        workingCell = Target.Address

        Dim undoList As String
        undoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
        If Left(undoList, 5) = "Paste" Then
            '--- grab the pasted value, then undo the action to
            '    restore the formula
            Dim pastedValue As Variant
            pastedValue = Target.Value
            With Application
                .EnableEvents = False
                .EnableEvents = True
            End With

            '--- now replace the pasted value into the formula
            Const START_OF_VALUE = 23
            Dim pos1 As Long
            Dim oldValue As String
            pos1 = InStr(START_OF_VALUE, Target.Formula, ",", vbTextCompare)
            oldValue = Mid(Target.Formula, START_OF_VALUE, pos1 - START_OF_VALUE)
            Target.Formula = Replace(Target.Formula, oldValue, pastedValue, , , vbTextCompare)
            Debug.Print "Replaced '" & oldValue & "' with '" & pastedValue & "'"
            workingCell = ""
        End If
    End If
End Sub
  • \$\begingroup\$ Thanks for your input, It does look to be valuable, and something I'll consider once I get this working the way I want; however, it doesn't really seem to address my question of how to improve the processing speed of the code I posted. As a matter of fact, it doesn't address my code at all. \$\endgroup\$ – FreeMan Oct 21 '16 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.