Forum Discussion
Protecting formulas in an Excel Table
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 SubHope I was able to help you with this info.
I know I don't know anything (Socrates)
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.
I know I don't know anything (Socrates)
- 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.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 :).
I know I don't know anything (Socrates)
Was the answer useful? Mark them as helpful!
This will help all forum participants.
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 = ""
