Protect unprotect formulas

Wimpie

Board Regular
Joined
Aug 12, 2008
Messages
207
Good day

Please assist with the below
I did a lot of research and found the below code that works , I only need all the cells with formulas protected and everything else to work
Code:
Sub Lock_Formulas()
    ActiveSheet.Protect "pass", True, True
    With ActiveSheet
     .Protect EnableOutlining = True
     .Protect UserInterfaceOnly = True
     .Protect DrawingObjects = True
     .Protect Contents = True
     .Protect Scenarios = True
     .Protect AllowFormattingColumns = True
     .Protect AllowFormattingRows = True
   End With
End Sub

The problem is that after unprotecting and protecting a few time I can not group (+) and ungroup (-) any more
How can I solve for this

I also need some code added to the below if the incorrect password as per the above is used to say "Incorrect password please try again" and to not create a Run-time error 1004 and direct me to developer to Debug
Code:
Sub Unlock_Formulas()
    ActiveSheet.Unprotect
End Sub

Thank you in advance
 
Last edited:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Wimpie

Board Regular
Joined
Aug 12, 2008
Messages
207
Good day

Found the below in Google
Does exactly what is needed.

Hope someone else can use it as well, see the below
Code:
Option Explicit
Sub LockAll()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        False, Password:="pass"
Next ws
End Sub
Sub UnLockAll()
    Dim ws As Worksheet
    Dim MyPassword As String
    MyPassword = InputBox("Enter Password.", "Password Entry")

    If MyPassword = "pass" Then
        For Each ws In Worksheets
            ws.Unprotect Password:="pass"
        Next ws
        
        MsgBox "All sheets are now unprotected.", vbCritical, "Sheets Unprotected"
    Else
        MsgBox "You entered an incorrect password.", , "Password Failed"
        Exit Sub
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,988
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top