Welcome to another #macromonday. This blog post is in answer to a question sent to me by a subscriber. They wanted to lock a cell once there was a value entered into it. That sounds like a great way to protect a cell once the user has updated the cell or cells you want them to. Now, there is no inbuilt feature in Excel to do this, but of course, we can write a little piece of VB code to do the job for use.
If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Want To Learn How Write Excel Macros? Check Out Chandoo’s VBA Classes.
What Does The Macro Do?
This Macro protect a cell or range of cells with a password that you specify in the code after the user has to input a value into those cells. In this example I want the user to enter their name into my worksheet specifically
How Does The Macro Work?
Step 1. First, we need to carry out a couple of steps before we get into writing some code. We need to unlock all of the cells on the worksheet before you write and dave the code. If you don’t then the cells with be locked even when they are empty.
- Hit CTRL+A to select your current range or hit CRL +A to select the whole sheet.
- Hit CTRL+1 to bring up the Format Cells dialog box and untick the Locked Cells option on the Protection tab.
- Select the cells you want to be affected by this Macro or your range of cells that you want to lock once the user has entered.
- Hit CTRL+1 to bring up the Format Cells dialog box and Tick the Locked Cells option on the Protection tab to protect those cell or cells. In this example, it is cell B4 where I want the user to enter their name
- Now we can start to write our Macro!
Step 2. Open up the Visual Basic Editor, Open Visual Basic – by hitting ALT +F11 or Developer Tab. We use Worksheet_Change Event which is an event procedure of the Worksheet Object, so it has to sit within the private module of the worksheet object. So, double-click on the relevant worksheet to access the code window.
Step 3. Select the Worksheet from the Object Drop Down Menu and Excel will automatically prepare the beginning of your code with the following syntax
Private Sub Worksheet_Change(ByVal Target As Range)
Step 4. We need to declare a variable. This will be the range of cells or cell in this example. This just allows Excel to allocate a memory container for this value. In our example, it is cell B4.
Dim MyRange As Range
Step 5. This step checks if the input was into the MyRange cells, in this case, cell B4, if it was then the worksheet is unprotected, the cell which was updated is locked and the worksheet is then protected.
Step 6. The routine then ends.
Step 7. Test Your Macro!!
If you want to use this code then you will need to change the password that you want to use for yourself if you want to unlock the worksheet manually at a later date.
WANT TO COPY THE CODE?