Forum Discussion
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
- borismarioCopper Contributor
Cyndie_Birdsong
I see that you resolved your issue, but I would like to add information to your solution:Maybe this is an official description of your issue.
VBA code "behind" a worksheet or a workbook may not work in Excel - Microsoft Support
- Cyndie_BirdsongCopper 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!
- PeterBartholomew1Silver Contributor
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_BirdsongCopper ContributorAnd 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_BirdsongCopper ContributorThanks. 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.
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_BirdsongCopper ContributorBut 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..I would have expected your code to stop at
AllowFormattingCells = True
since that isn't valid by itself.