Macro runs on protected sheet but changes "Protect Sheet" options

[Andrew B]

I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.

My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?

 

[Michael M]

When your code protects after doing it's work try adding

    With ActiveSheet
        .Protect AllowFormattingCells:=True
        .EnableSelection = xlUnlockedCells
    End With

 

[Andrew B]

First, let me say thank you for your reply and for trying to help me.

I tried the "With" statement but it didn't work. It's very possible that I've done something wrong as my skill level is not very high. Here's what I tried:

I started with my simplest macro and inserted the code this way:

Sub SortByTime()
    ActiveSheet.Unprotect Password:="mypassword"
    Range("A1:K19999").Sort Key1:=Range("B1"), _
      Order1:=xlAscending, Header:=xlGuess, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
    ActiveSheet.Protect Password:="mypassword"

   [B] With ActiveSheet
        .Protect AllowFormattingCells:=True
        .EnableSelection = xlUnlockedCells
    End With[/B]
   
End Sub

But after I ran the macro the sheet was left unprotected, so I tried it this way

Sub SortByTime()
    ActiveSheet.Unprotect Password:="mypassword"
    Range("A1:K19999").Sort Key1:=Range("B1"), _
      Order1:=xlAscending, Header:=xlGuess, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
    
   [B] With ActiveSheet
        .Protect AllowFormattingCells:=True
        .EnableSelection = xlUnlockedCells
    End With[/B]
ActiveSheet.Protect Password:="mypassword"
   
End Sub

And after I ran the macro my sheet was protected, but the same problem remained of not being able to format the unlocked cells.

I also tried placing ActiveSheet.Protect Password:="mypassword" within the "With" statement but when I put it right after "With ActiveSheet" I got the unprotected sheet result and when I put it right before "End With" I got the protected without formatting options result.

Any ideas? Please forgive me for being so clueless!

Sincerely,
Andrew

 

[engr76vpi]

When your code protects after doing it's work try adding

    With ActiveSheet
        .Protect AllowFormattingCells:=True
        .EnableSelection = xlUnlockedCells
    End With

Michael, I have the same identical problem as originally listed by Andrew and I tried your fix, but just like Andrew, I still cannot FORMAT cells after running the macro.
I am running Excel 2010. In my case, I'm inserting a row, copying the formulas above it and inserting the formulas in the new row. Here's the ORIGINAL macro......

 Sub InsertRowAbove_CopyFormulaFromAbove()
'
' InsertRowAbove_CopyFormulaFromAbove Macro
'
' Keyboard Shortcut: Ctrl+i
'
ActiveSheet.Unprotect "cbi"
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 6).Range("A1:AV1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Protect "cbi"

End Sub

Here's the modified macro with the coding you suggested inserted...... I am trying to select Protect Options
Select Locked Cells <> Select Unlocked Cells <> Allow Formatting

Sub InsertRowAbove_CopyFormulaFromAbove()
'
' InsertRowAbove_CopyFormulaFromAbove Macro
'
' Keyboard Shortcut: Ctrl+i
'
'
ActiveSheet.Unprotect "password"
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 6).Range("A1:AV1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Protect AllowFormattingCells:=True
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.EnableSelection = xlLockedCells
ActiveSheet.Protect "password"

End Sub

After running the macro the sheet re-protects but I cannot access the FORMATTING functions

Thanks in advance for any help you can provide.
Ted

 

[RoryA]

Hi, Ted, and welcome to the forum.

Replace this:

ActiveSheet.Protect AllowFormattingCells:=True
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.EnableSelection = xlLockedCells
ActiveSheet.Protect "password"

with this:

ActiveSheet.EnableSelection = xlNoRestrictions
ActiveSheet.Protect Password:="password", AllowFormattingCells:=True