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 ...
excel
Formulas and Functions
Macros and VBA
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..
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Mar 28, 2021

      Cyndie_Birdsong 

      I would have expected your code to stop at

       

      AllowFormattingCells = True

       

      since that isn't valid by itself.

      • Cyndie_Birdsong's avatar
        Cyndie_Birdsong
        Copper Contributor
        Mar 28, 2021
        thanks - so that's supposed to go after I re-protect the sheet? I do have them unlocked via the Format Cells settings, so do I even need that part? I really just need to unlock the protection to run the code and then re-protect at the end. I also tried adding UserInterFaceOnly = true but that didn't work either. I'm sure it's something stupid that I'm missing.. 😉

Resources