Forum Discussion

wcstarks's avatar
wcstarks
Iron Contributor
May 01, 2022
Solved

Protecting formulas in an Excel Table

I have an Excel table containing a number of columns with formulas. I insert new rows at the top to keep the most current rows first. I normally insert new rows at row 3, but may insert below there a...
excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    May 01, 2022

    wcstarks 

    Your message does not show which Excel version, operating system and storage medium it is. Nevertheless, here is a small VBA example of how it could work.

     

    Option Explicit

Sub ProtectMe()
Dim r As Range
Dim myPass As String
Dim wKs As Worksheet
myPass = "1234"
For Each wKs In ActiveWorkbook.Worksheets
With wKs
On Error GoTo myErrPass
.Unprotect myPass
On Error Resume Next
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = True
On Error GoTo 0
.Protect myPass
End With
Next wKs
End
myErrPass:
MsgBox ("Abort - Wrong password")
End
End Sub

     

     

    Hope I was able to help you with this info.

     

    NikolinoDE

    I know I don't know anything (Socrates)

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 01, 2022

wcstarks 

Your message does not show which Excel version, operating system and storage medium it is. Nevertheless, here is a small VBA example of how it could work.

 

Option Explicit

Sub ProtectMe()
Dim r As Range
Dim myPass As String
Dim wKs As Worksheet
myPass = "1234"
For Each wKs In ActiveWorkbook.Worksheets
With wKs
On Error GoTo myErrPass
.Unprotect myPass
On Error Resume Next
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = True
On Error GoTo 0
.Protect myPass
End With
Next wKs
End
myErrPass:
MsgBox ("Abort - Wrong password")
End
End Sub

 

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

Resources