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

Get FREE Advanced Excel Exercises with Solutions!

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.

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


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.

Using Password

  • Then, from the Format Cells dialogue box go to the Protection Uncheck the option Locked.
  • Now click on OK.

Using Password

  • Furthermore, right-click on the sheet tab ‘With Password’. Select the option View Code.

Using Password

  • 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.

Using Password to Lock a Cell after Data Entry Using VBA

  • Create a macro to run the code. We created a macro named VBA.
  • Then, select VBA and click on Run.

Using Password to Lock a Cell after Data Entry Using VBA

  • 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.

Without Password

  • Then, go to the Protection tab in the Format cells dialogue box. Uncheck the Locked option.
  • Now, click on OK.

Without Password

  • Additionally, right-click on the ‘Without Password‘ sheet tab. Select the View Code.

Without Password

  • 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.

Without Password

  • Afterward, to run the code, create a macro. We created a macro named VBA2.
  • So, choose VBA2 and press the Run button.

Without Password

  • 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.

Read More: Excel VBA to Lock Cells without Protecting Sheet


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.

Insert Excel VBA for Specific Cell Range to Lock a Cell after Data Entry and Show Notification in Message Box

  • Thirdly, in the Format Cells dialogue box, go to the Protection Make sure that the Locked box is unchecked.
  • Now press the OK button.

Insert Excel VBA for Specific Cell Range to Lock a Cell after Data Entry and Show Notification in Message Box

  • Next, right-click on the sheet named Range. Select the option View Code.

Insert Excel VBA for Specific Cell Range to Lock a Cell after Data Entry and Show Notification in Message Box

  • 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.

Insert Excel VBA for Specific Cell Range to Lock a Cell after Data Entry and Show Notification in Message Box

  • Furthermore, create a macro named VBA3. Select that macro and click on the Run button.

Insert Excel VBA for Specific Cell Range to Lock a Cell after Data Entry and Show Notification in Message Box

  • 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).

Read More: Excel VBA to Protect Range of Cells


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

6 Comments
  1. 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!

  2. Hello,

    Nice article.

    How to block cells from entering data, can’t type anything there or edit?

    Thanks

    • Reply Avatar photo
      Musiha Mahfuza Mukta May 3, 2023 at 11:28 AM

      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.
      2-How-to-block-cells-from-entering-data
      • 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.

  3. 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-

      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

      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.

      Sub Lock_CellRange_AllSheets()
          Dim ws As Worksheet
          For Each ws In ActiveWorkbook.Worksheets
              ws.Unprotect Password:="1234" 'Replace the password if you want
              ws.Range("D8:F12").Locked = True 'Replace the cell range
              ws.Protect Password:="1234", UserInterfaceOnly:=True 'Replace the password with the actual password for the workbook if it is protected
          Next ws
      End Sub

      Again, try this code to unlock the specific cell range in all worksheets.

      Sub Unlock_CellRange_AllSheets()
          Dim ws As Worksheet
          For Each ws In ActiveWorkbook.Worksheets
              ws.Unprotect Password:="1234" 'Replace the password if you want
              ws.Range("D8:F12").Locked = False 'Replace the cell range
              ws.Protect Password:="1234", UserInterfaceOnly:=True 'Replace the password with the actual password for the workbook if it is protected
          Next ws
      End Sub

      I hope these will work! Moreover, you can follow lock and unlock cells using VBA to explore more efficient methods.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo