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

excel
Formulas and Functions
Macros and VBA

10 Replies

Sort By
  • Cyndie_Birdsong's avatar
    Cyndie_Birdsong
    Copper Contributor
    Apr 09, 2021

    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!

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 28, 2021

    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
      Mar 28, 2021
      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
      Mar 28, 2021
      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.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 28, 2021

    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
      Mar 28, 2021
      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