Macro Monday time and that means some more Excel macros and VBA. Today let’s look at a tiny Macro that pulls a punch.
I used this Macro myself last week whilst creating a spreadsheet solution for a colleague and I needed to unlock all of the cells in all of the worksheets (14 in total!) in a workbook, so I could then lock and protect a set of cells in all of the worksheets. ( the first stage of protecting specific cells – is to unlock them, if they are not already unlocked, to lock them and then enable these changes by protecting the Excel worksheet.
Download Macro Mondays Example Files.
What Does The Macro Do?
This Macro will unlock all of the cells in each worksheet in an Excel workbook.
How Does It Work?
The Macro uses the Worksheet.Cells property which returns a range object that represents all the cells on the worksheet as we have not specified any row or column index after the Cells keyword. We use this to set the status of all cells in the workbook to unlocked. Let’s get coding!
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Module
Step 1. First we declare a variable, ws as a worksheet object. This will create a memory container in Excel for each worksheet the macro loops through.
Step 2. Excel begins looping through the worksheets in the workbook. We use Active.Workbook as we want the code to run in the workbook we are actively using.
Step 3. This switches the status of the cells to unlocked.
Step 4. The last stage is to loop back to the next sheet, where the procedure is repeated, until all of the Excel worksheets cells are unlocked and the Macro ends.
Step 5. Test your Macro.
Here is the code if you want to copy and paste it into Excel.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
.Cells.Locked = False
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.