Forum Discussion

Cyndie_Birdsong's avatar
Cyndie_Birdsong
Copper Contributor
Mar 28, 2021

Having issue with Activesheet.Unprotect allowing all VBA code to run.

I have some macros on a sheet that is protected as it's set up like a user form, which I have protected so it doesn't get messed up by the various users. I've unlocked the cells they can modify, and protected the sheet to allow for other users to select unlocked cells. I created the macro based on recording my keystrokes via Excel's Macro Recorder, and it works fine if I don't protect the worksheet at all. Once I added the protection, it always hangs up at the Selection.PasteSpecial line. 

 

'Unprotect the worksheet
ActiveSheet.Unprotect
AllowFormattingCells = True
'
Range("AR5").Select
Selection.Copy
Range("G5:AC21").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

10 Replies

  • Cyndie_Birdsong's avatar
    Cyndie_Birdsong
    Copper Contributor

    I was finally able to get the code working - for anyone interested, I left the unprotect/protect code out of all the macros and only put it on the main VBA code for the workbook, and now it works great. Thanks all for your help!

  • Cyndie_Birdsong 

    I believe HansVogelaar has provided the solution to your problem, but you might like to take a further step beyond macro recorder.  Unless it is important to you to transfer the font colour, size, cell colour, conditional formats, protection etc., you could simply transfer the values (or, I think in this case, the formula)

     

        'Unprotect the worksheet
        ActiveSheet.Unprotect
        'Copy formulas
        Range("G5:AC21") = Range("AR5").FormulaR1C1
        ' Protect the sheet again
        ActiveSheet.Protect AllowFormattingCells:=True

     

    Personally, I would also use defined Names, just in case you wish to extend the Range "G5:AC21" or someone moves the cell "AR5".  It is very easy to forget to adjust the VBA otherwise.

    • Cyndie_Birdsong's avatar
      Cyndie_Birdsong
      Copper Contributor
      And the AR5 cell is behind protection so they can't move it. The main reason I want to protect the sheet is so users can't move anything, only fill in unlocked cells. thanks..
    • Cyndie_Birdsong's avatar
      Cyndie_Birdsong
      Copper Contributor
      Thanks. I do have the Activesheet.unprotect at the beginning and the copy needs to be a PasteSpecial, Formula only, not values. It's just hanging up at that line I mentioned above.
  • Cyndie_Birdsong 

    You can unprotect the worksheet, perform actions that are only allowed in an unprotected sheet, then protect the sheet again:

     

        'Unprotect the worksheet
        ActiveSheet.Unprotect
        ' Copy and paste formulas
        Range("AR5").Copy
        Range("G5:AC21").PasteSpecial Paste:=xlPasteFormulas
        ' Protect the sheet again
        ActiveSheet.Protect AllowFormattingCells:=True
    • Cyndie_Birdsong's avatar
      Cyndie_Birdsong
      Copper Contributor
      But that's what I'm doing, I have ActiveSheet.Unprotect at the beginning of that sub & ActiveSheet.Protect at the end. Are you saying to eliminate this part?
      Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False

      There is more code in that sub but this was the line it was hanging up at..

Resources