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 as needed to enter missed data. Row two is hidden to address formatting issues when inserting next to the header row. I need to protect all the formulas while allowing insertions and edits to the non-formula columns.

 

I understand that doing so with tables may be different than doing so with normal spread sheets. How would this be done.

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)

6 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold 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)

    • wcstarks's avatar
      wcstarks
      Iron Contributor
      May 01, 2022
      Thanks for your response. I guess it must be more involved than I had imagined--requiring VBA. I am using the current Office 365 products on Windows 11. I have a couple of questions/comments:
      1) I am not familiar with how to insert VBA code into Excel.
      2) What do the "."s mean at the beginning of certain lines?
      3) This apparently does this for all worksheets in the book.
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        May 01, 2022

        wcstarks 

        Here is a file with the code so you can see how it is inserted.

        Steps:

        Press the Alt + F11 key combination to open the VBA development

        environment window

        Open the project explorer with the key combination Ctrl + R

        and activate there double-click on the desired file, it is then

        highlighted in blue now double-click on "this workbook" below this file

        now the code window of "this workbook" is in the right half of the window.

        The code can be inserted into this

        save and close Visual Basic with key combination Alt + F4

        the macro is now available in the file

         

        Additional Info:

        Getting started with VBA in Office

        VBA code "behind" a worksheet or a workbook may not work in Excel

         

        Hope I was able to help you :).

         

        NikolinoDE

        I know I don't know anything (Socrates)

        Was the answer useful? Mark them as helpful!

        This will help all forum participants.

Resources