Forum Discussion

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

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

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

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.

VBA_protect_cells_with_formulas.xlsm16 KB

wcstarks
Iron Contributor
May 01, 2022
OK. I have created the VBA code as indicated and saved it as an xlsm. How do I activate the code?
Are those leading "."s on some of the code lines valid?
If the workbook is not password protected, I presume I can say MyPass = ""
- NikolinoDE
  Gold Contributor
  May 02, 2022
  wcstarks
  I have created the VBA code as indicated and saved it as an xlsm. How do I activate the code?
  You don't need to do anything to activate it, just opening the worksheet activates the code.
  Are those leading "."s on some of the code lines valid?
  in VBA the dot at the beginning of an expression has the following function:
  If an object was specified with a WITH command in the previous code, you can replace this object with the dot in the following.
  ie:
  Code:
  With Sheets("Sheet1")
  .Cells(1,1).Clear
  End With
  
  stands for:
  Code:
  Sheets("Sheet1").Cells(1,1).Clear
  this saves a lot of typing for frequently used objects.
  
  If the workbook is not password protected, I presume I can say MyPass = ""
  there you can use your own password instead of 1234.
  
  I wish you continued success with Excel
  NikolinoDE
  - wcstarks
    Iron Contributor
    May 02, 2022
    Thanks for that helpful information. While I design the work book and do some data entry on my computer, much of my daily data entry is done from the phone Excel app. I discovered yesterday that the phone app cannot open a file with the extension of .xlsm. So, unfortunately, I guess this solution will not work for how I need to use it. Thank you for taking the time to help me. I learned a few things from this exercise.