Welcome to another #macromonday blog post in this series. This blog post is in answer to a question sent to me by a subscriber. They wanted to know how 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. Let’s get coding, shall we?.

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?

 

Preparation Before Writing The VBA Code

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!

Starting The Macro

Open up the Visual Basic Editor, Open Visual Basic – by hitting ALT +F11 or Developer Tab.

The Worksheet_Change Event

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. Select the Worksheet from the Object Drop Down Menu and Excel will automatically prepare the beginning of your code with the following syntax

[stextbox id=’info’]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub[/stextbox]

 

Declaring A Variable

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.

[stextbox id=’info’]

Dim MyRange As Range

Set MyRange = Range (“B4”)

[/stextbox]

 

Checking If The Input Range Met The Criteria/Protecting The Cell

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.

[stextbox id=’info’]

If Not MyRange Is Nothing Then

Sheets (“Sheet1”). Unprotect Password := “mypassword”

MyRange. Locked = True

Sheets (“Sheet1”). Protect Password := “mypassword”

End If

[/stextbox]

 

The Routine Ends.

[stextbox id=’info’]

End Sub

[/stextbox]

Test Your Macro!!

Of course, 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?

[stextbox id=”grey”]

‘Macro by How To Excel At Excel’
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range

Set MyRange = Range(“B4”)
If Not MyRange Is Nothing Then
Sheets(“Sheet1″).Unprotect Password:=”mypassword”
MyRange.Locked = True
Sheets(“Sheet1″).Protect Password:=”mypassword”
End If

End Sub[/stextbox]

 

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

Macro Mondays Blog Posts.

 

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.