Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking

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.

Related Articles

• How to Lock and Unlock Cells in Excel Using VBA
• Excel VBA to Lock Cells without Protecting Sheet
• How to Protect Specific Columns Using VBA in Excel
• How to Hide Formula in Excel Using VBA