Welcome back to another blog post in my 2019 series of #macromonday where I share some awesome Excel tips. Today let’s look at how to write an Excel VBA Macro to quickly protect any cell in your worksheet which contains a formula.
Most often if you are sharing an Excel workbook you really do not want users to be able to edit or change your cells that have formulas. This macro is a really quick and easy way to protect your formula cells. You can use it again and again in any of your Excel workbooks by making it available whenever you are in the Excel application. More of that later though. Let’s get coding our Excel macro!.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice. You can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Starting The Macro
Let’s get today’s macro started. We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module.
Type Sub then the name of your macro. In this example, I have called the macro ProtectFormulaCells. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
[stextbox id=’info’]Sub ProtectFormulaCells
End Sub[/stextbox]
Declaring Variables
The next step in our macro is to declare any variables we may need. This ensures that Excel creates a memory container for these values. In this example, we have only one variable to declare.
[stextbox id=’info’] Dim sh as Worksheet
[/stextbox]
Ignoring Errors
We now instruct Excel to move to the next line of code if any errors are generated. Use this with caution. Not all errors should be ignored as they can give really useful information/ issues with your code. But in this case, I am ok with ignoring any errors.
Using For Each Loop in Excel VBA
Next, we instruct Excel to use the For Each Looping command. We start with For Each and then need a variable name. In this example, it is sh. This represents our worksheets. VBA will store individual items into this variable. After the variable name, we use the word “In". Finally, follow that with the name of the collection or array you’re trying to access. In this example, it is ActiveWorkbook.Sheets.
So, a series of instructions are carried out for each of the worksheets the active workbook. These are:-
Worksheets are unprotected | Cells are unlocked | Cells with formulas are locked | Worksheets are protected.
[stextbox id=’info’] For Each sh In ActiveWorkbook.Sheets
With sh
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect
End With
Next sh
[/stextbox]
Ending The Macro
This process continues until all of the worksheets in the active workbook have been looped. Once Excel has done this the routine ends with the End Sub instruction. This was already entered into the module for us when started the type the name of the macro.
[stextbox id=’info’] End Sub
[/stextbox]
More Related #MacroMonday Blog Posts
Create Multiple Worksheets From A List Of Cell Values – Using A VBA Excel Macro
Write A Macro Which Highlights Named Ranges. Macro Monday.
If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
