I need how change vba code

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
74
Office Version
  1. 365
Platform
  1. Windows
This vba code is to protect formulas and worksheets

I want to unprotect worksheet with vba code

VBA Code:
Sub protectFormulasAndEnableProtection()

Dim sht As Worksheet

On Error Resume Next

For Each sht In Sheets
    sht.Activate
    sht.Unprotect
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    If Err.Number = 1004 Then
        'No special cells found (no formulas in this case). Clear the error and take no action
        Err.Clear
    Else
        Selection.Locked = True
        Selection.FormulaHidden = False
    End If
    sht.Protect AllowFormattingColumns:=True, AllowFormattingRows:=True
Next sht

End Sub


Thanks you

Thomas
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
You're already unprotecting & re-protecting; are the sheets password protected?

VBA Code:
Sub protectFormulasAndEnableProtection()

Dim sht As Worksheet

On Error Resume Next

For Each sht In Sheets
    sht.Activate
    sht.Unprotect "password"
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    If Err.Number = 1004 Then
        'No special cells found (no formulas in this case). Clear the error and take no action
        Err.Clear
    Else
        Selection.Locked = True
        Selection.FormulaHidden = False
    End If
    sht.Protect "password"  AllowFormattingColumns:=True, AllowFormattingRows:=True
Next sht

End Sub
 

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
74
Office Version
  1. 365
Platform
  1. Windows
The code I post it protect all worksheets that have any formula. I want code that do opposite code do

thanks you

Thomas
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
If you just want to unprotect all sheets try
VBA Code:
Sub TLS()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Ws.Unprotect
   Next Ws
End Sub
 

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
74
Office Version
  1. 365
Platform
  1. Windows
If you just want to unprotect all sheets try
VBA Code:
Sub TLS()
   Dim Ws As Worksheet
  
   For Each Ws In Worksheets
      Ws.Unprotect
   Next Ws
End Sub
Thanks you Fluff
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,577
Messages
5,548,861
Members
410,881
Latest member
toonces
Top