This tutorial will demonstrate how to lock a cell after data entry using Excel VBA with a message box notification before locking. So users will not be able to change the value after entry. If they try, a message box notification will appear. Sometimes we need to lock a cell after data entry. We do this to restrict users from changing data frequently.
How to Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking: 2 Methods
In this article, we will use 2 methods to lock a cell after data entry using Excel VBA. After locking a cell users will not be able to change data. A message box notification will appear if any user tries to edit the data after entry. To illustrate these two methods we will use the following dataset. In the dataset, users will input the values of the sales amount. But, they can enter a value in a particular cell just once. If a user tries to change the value a message box notification will appear.
1. Using Password or Without Password to Lock a Cell
1.1 Using Password
First and foremost, we will lock a cell after data entry with a password. We will do this using Excel VBA code. Any kind of attempt to change data after entry will show a message box notification. Let’s see the steps to perform this method:
STEPS:
- To begin with, select the data range (B4:D9).
- In addition, right-click on the selected area. Click on the option Format Cells.
- Then, from the Format Cells dialogue box go to the Protection Uncheck the option Locked.
- Now click on OK.
- Furthermore, right-click on the sheet tab ‘With Password’. Select the option View Code.
- The above command will open a blank VBA code window.
- Next, type the following code in that code window:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
ActiveSheet.Unprotect Password:="1234"
For Each aCell In Target
If aCell.Value <> "" Then
Trial = MsgBox("Is this value correct? You can not change after entering a value.", _
vbYesNo, "Message Box Notifation")
If Trial = vbYes Then
aCell.Locked = True
Else
well.Value = ""
End If
End If
Next aCell
ActiveSheet.Protect Password:="1234"
End Sub
- Click on the Run button or press the F5 key to run the code.
- Create a macro to run the code. We created a macro named VBA.
- Then, select VBA and click on Run.
- Afterward, enter a value in cell D5.
- A message box appears. It asks whether the value is right or not. Because we can not change the value once we make an entry in that cell.
- After that, try to edit cell D5.
- We will receive a warning message like the following image.
- Moreover, to edit the value again we have to unprotect the sheet.
- So, got to Review > Unprotect Sheet.
- Then, type the password you used in the VBA In this example, our password is 1234.
- Now, click on OK.
- Lastly, click on cell D5 Cell D5 is unlocked now. So, we can change the value after making an entry.
1.2 Without Password
This method is mostly identical to the previous method. We will also lock a cell after data entry using Excel VBA in this method. But, this time we will not use a password in our VBA code. Like the previous one, a message box notification will appear. Follow the below steps to perform this action:
STEPS:
- First, choose a data range (B4:D9).
- Next, right-click the area you want to edit. Select the Format Cells option.
- Then, go to the Protection tab in the Format cells dialogue box. Uncheck the Locked option.
- Now, click on OK.
- Additionally, right-click on the ‘Without Password‘ sheet tab. Select the View Code.
- The above action will open a blank VBA code window.
- After that, insert the following code in that code window:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
ActiveSheet.Unprotect
For Each aCell In Target
If aCell.Value <> "" Then
Trial = MsgBox("Is this value correct? You can not change after entering a value.", _
vbYesNo, "Message Box Notifation")
If Trial = vbYes Then
aCell.Locked = True
Else
aCell.Value = ""
End If
End If
Next aCell
ActiveSheet.Protect
End Sub
- To run the code, press the F5 key or click the Run button.
- Afterward, to run the code, create a macro. We created a macro named VBA2.
- So, choose VBA2 and press the Run button.
- Moreover, input a value in cell D5.
- As a result, we can see a message box. It inquires as to whether the value is appropriate or not. We can’t edit the value once we’ve entered it in that cell.
- After that, try editing cell D5 once more.
- We will receive a message box similar to the image below.
- Following, to re-edit the value, we must first unprotect the sheet.
- So, you’ll need to go to Review > Unprotect Sheet.
- This time we do not require any password to unprotect the sheet.
- Finally, click on cell D5 once more. Cell D5 is now unlocked. So, after entering a value, we can edit it.
2. Locking a Specific Cell Range
In the last method, we will apply Excel VBA for locking a specific cell range. So, we will be able to lock a cell after data entry only for that cell range. Also, any kind of attempt to change after entering data in that range will show a notification in the message box. Let’s see the steps to perform this action.
STEPS:
- Firstly, select the cell range (D5:D9).
- Next, right-click on the selected range. Select the Format Cells option.
- Thirdly, in the Format Cells dialogue box, go to the Protection Make sure that the Locked box is unchecked.
- Now press the OKÂ button.
- Next, right-click on the sheet named Range. Select the option View Code.
- The above command opens a VBA code window.
- Then, type the following code in that blank code window:
Dim zRg As Range
Dim zStr As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("D5:D9"), Target) Is Nothing Then
Set zRg = Target.Item(1)
zStr = zRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aRg As Range
On Error Resume Next
Set aRg = Intersect(Range("D5:D9"), Target)
If aRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="1234"
If aRg.Value <> mStr Then aRg.Locked = True
Target.Worksheet.Protect Password:="1234"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("D5:D9"), Target) Is Nothing Then
Set zRg = Target.Item(1)
zStr = zRg.Value
End If
End Sub
- Now, click on the Run button or press the F5 key to run the code.
- Furthermore, create a macro named VBA3. Select that macro and click on the Run button.
- After that, enter a value in cell D5.
- In the end, try to edit the value of cell D5 A message box like the following image will appear. But we will be able to edit the values outside the cell range (D5:D9).
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In conclusion, this tutorial demonstrates 2 methods to lock a cell after data entry using Excel VBA. Any attempt to edit the value of the locked cell will show a notification in a message box. Download the practice worksheet contained in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to respond to your message as soon as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.
Hi Mukesh
Nice article.
My query is – I don’t want to password-protect the sheet right after entry. But after 1 hr or after the sheet has been closed. Is there a possible way to do that?
Thanks
Hello, ADITYA AGARWAL!
Try This code. This will automatically protect your spreadsheet after the sheet has been closed.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WrkSht As Worksheet
Const Password As String = “pass1234”
For Each WrkSht In ThisWorkbook.Worksheets
WrkSht.Protect Password:=Password
Next WrkSht
End Sub
Hope this will help you!
Hello,
Nice article.
How to block cells from entering data, can’t type anything there or edit?
Thanks
Thank you, EAC for your comment. The possible solution is given below.




• Select all the cells by clicking the triangle where row and column headers coincide.
• Next, open the Format Cells by pressing Ctrl+1 >> Select the Protection option >> Uncheck the Locked option to unlock cells >> Click on OK.
• Select the data range which you want to lock.
• Again, press Ctrl+1 >> The Format Cells dialog box will pop up >> Select Protection >> Next check on the Locked option >> Click on OK.
• Go to the Review tab in the ribbon >> Select Protect Sheet from the Protect group.
• A Protect Sheet dialog box will appear >> Set any password in the password box >> Check on the Protect worksheet and contents of locked cells>> Check both Select locked cells, Select unlocked cells.
• A Confirm Password dialog box will appear >> Rewrite your given password >> Click on OK.
Now, try to edit the cells. Then you will get a warning from Microsoft Excel that you can’t change anything. To edit or enter any value, you have unprotected the Excel sheet with that password first.
Furthermore, you can see this article for more details How to Protect Excel Cells from Being Edited.
Hello,
I wrote a program that locks and unlocks a given range, however, when trying for all sheets it does not work, how to correct this code? Because each sheet is the same and want to have the same range locked.
Sub protect()
Columns(“D:F”).Select
Selection.Locked = False
Range(“D8:F12”).Select
Selection.Locked = True
Range(“a1”).Select
End Sub
Sub block()
Dim hz As String
Dim hz1 As String
hz = InputBox(“enter your password”)
hz1 = InputBox(“repeat password”)
If hz hz1 Then
MsgBox “The repeated password is not identical!”, vbExclamation
hz = “”
hz1 = “”
Else
Columns(“D:F”).Select
Selection.Locked = False
Range(“D8:F12”).Select
Selection.Locked = True
Range(“a1”).Select
ActiveSheet.protect hz
End If
End Sub
Sub unblock()
Dim ho As String
On Error GoTo err
ho = InputBox(“Enter password to unlock”)
ActiveSheet.Unprotect ho
Columns(“D:F”).Select
Selection.Locked = False
Range(“D8:F12”).Select
Selection.Locked = False
Range(“a1”).Select
Exit Sub
err:
MsgBox “The password you entered is incorrect”, vbExclamation
End Sub
Hello EAC,
Thanks for your question. Most likely, you need to put a not equal sign (<>) inside the If…Then statement of your second code. So, the corrected second code will be as follows-
However, your used three codes are quite messy. I would like to suggest that you can use the following code to lock a specific cell range in all worksheets of your Excel file.
Again, try this code to unlock the specific cell range in all worksheets.
I hope these will work! Moreover, you can follow lock and unlock cells using VBA to explore more efficient methods.