How to Lock a Cell in Excel After Data Input Using VBA?

Sharing is caring!

Are you looking for a way to lock a cell in Excel after data input using VBA? Locking cells after data entry is a useful technique to prevent accidental changes or edits to important data in your Excel worksheets. In this article, we’ll explain exactly how you can use Excel VBA to automatically lock cells once data has been entered into them.

Why Lock Cells After Data Entry?

There are several reasons why you might want to lock cells after data has been input:

• Prevent accidental edits or deletions: Locking cells ensures that data cannot be accidentally changed or removed. This is especially important for critical data.
• Preserve data integrity: Locking cells maintains the accuracy and consistency of your Excel data. It prevents invalid entries from being made.
• Avoid formula errors: If formulas reference locked cells, it prevents issues that can arise if those referenced cells are changed.
• Improve usability: Locking cells provides a visual cue to users that the data should not be edited. It makes your spreadsheets more user-friendly.

How to Lock Cells in Excel

Before diving into the VBA code to automatically lock cells after data entry, let’s review the standard ways to manually lock cells in Excel:

1. Select the cell or range of cells you want to lock
2. Right-click and select “Format Cells” from the context menu
3. Go to the “Protection” tab
4. Check the “Locked” box
5. Click “OK”
6. Protect the sheet by going to Review > Protect Sheet

This will lock the selected cells and prevent them from being edited. The cells will need to be unlocked before any changes can be made.

However, this standard cell locking method requires manually locking cells. It doesn’t automatically lock them after data is entered. Let’s see how to use VBA to improve this process.

VBA Code to Lock Cells After Data Input

Here’s the VBA code you can use in Excel to lock a cell immediately after data is entered into it:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        cell.Locked = True
    Next cell
    Me.Protect
End Sub

To use this code:

1. Right-click on the worksheet tab and select “View Code”
2. Paste the above code into the sheet module
3. Save and close the Visual Basic Editor
4. Enter data into any cells
5. The cells will automatically lock after data is entered

Here’s a step-by-step breakdown of how this VBA code works:

• The Worksheet_Change event is triggered whenever any changes are made to the worksheet
• The Target argument represents the cell or cells that were changed
• We loop through each cell in the Target range
• The Locked property is set to True for each changed cell, which locks it
• The Protect method is called to protect the sheet and activate the cell locks

So in just a few lines of code, we can set up automatic cell locking in Excel when data is entered! The cells will be protected from any further changes.

Unlocking Specific Cells

What if you want most of your worksheet locked, but need a few specific cells to remain unlocked for data entry? You can achieve this by modifying the cell locking VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        If Intersect(cell, Range("A1:A10")) Is Nothing Then
            cell.Locked = True
        End If
    Next cell
    Me.Protect
End Sub  

In this updated code:

• An If statement checks if the changed cell intersects with the range A1:A10
• If there is no intersection, the cell is locked (so all cells outside of A1:A10)
• Cells inside the range A1:A10 remain unlocked for editing after the sheet is protected

So this allows you to keep specific data entry cells unlocked while protecting the rest of the sheet. Simply adjust the reference range to correspond to your unlocked cells.

Locking Cells Based on Criteria

You can also use VBA to conditionally lock cells in Excel based on certain criteria. For example, let’s say you only want to lock cells in column D if the adjacent cell in column E is not blank:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        If Not Intersect(cell, Range("D:D")) Is Nothing Then
            If cell.Offset(0, 1).Value <> "" Then
                cell.Locked = True
            End If
        End If
    Next cell
    Me.Protect
End Sub

Breaking this down:

• The If statement first checks if the changed cell is in column D
• If so, another If checks if the adjacent cell in column E is not blank
• The cell is locked only if both conditions are met

This allows you to apply conditional logic to determine when cells should be locked. You can customize the conditions based on your specific requirements.

Locking Cells with Data Validation

Excel’s Data Validation feature is useful for restricting cell inputs to specific values. We can integrate data validation with VBA cell locking for even more control over data entry.

Here’s an example that locks any cell in column A if a value from the validation list is selected:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        If Not Intersect(cell, Range("A:A")) Is Nothing Then
            If cell.Value <> "" Then
                cell.Locked = True
            End If
        End If
    Next cell
    Me.Protect
End Sub

To set this up:

1. Select column A and go to Data > Data Validation
2. Choose “List” and input comma-separated values or a range reference
3. The VBA code will lock any cell in column A once a valid list item is selected

Combining data validation with cell locking helps ensure that cells are locked only when valid entries are made. It’s a great way to enforce data accuracy.

Locking Cells on Protected Worksheets

If you want to allow users to input data on an already protected worksheet, you’ll need to unlock the specific data entry cells first. Then you can use VBA to lock those cells after data is entered.

Here’s the modified VBA code for protected worksheets:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Me.ProtectContents Then
        Me.Unprotect ("SecretPassword")
        For Each cell In Target
            cell.Locked = True
        Next cell
        Me.Protect ("SecretPassword")
    End If
End Sub

Important points:

• The If statement checks if the worksheet is currently protected
• If so, the Unprotect method is called with the worksheet password
• After locking the changed cells, the Protect method re-protects the worksheet
• Be sure to replace “SecretPassword” with your actual worksheet password

This code ensures that the sheet is unprotected before locking cells, and re-protected afterwards. This is necessary to modify cells on a protected worksheet.

Additional Considerations

Here are a few more tips and considerations to keep in mind when locking cells after data input with VBA:

• Worksheet password: Always use a strong password to protect your worksheets. Avoid easily guessed passwords.
• Cell formatting: Locked cells can still be formatted. If you don’t want locked cells to be reformatted, you’ll need to use the FormatConditionsCollection.Delete method.
• New workbooks: Excel does not automatically enable cell locking in new workbooks. Be sure to lock cells in your worksheet templates.
• Unlocking cells: If you need to make changes to locked cells, simply unprotect the sheet. You can rerun the VBA code to relock cells after editing.
• Compatibility: The VBA code in this article should work for all modern versions of Excel, including Excel 2019, Excel 2021, and Microsoft 365.

Final Thoughts

Locking cells in Excel after data entry is a handy way to protect your worksheets from accidental edits. While you can manually lock cells, VBA provides a way to automatically lock them as soon as data is entered.

The VBA code is straightforward to implement. Simply insert it into the sheet code module and it will instantly lock cells when data is input. You can customize the code to leave specific cells unlocked, apply conditional locking criteria, or integrate it with data validation.

FAQs

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        cell.Locked = True
    Next cell
    Me.Protect
End Sub
      

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        If Not Intersect(cell, Range("D:D")) Is Nothing Then
            If cell.Offset(0, 1).Value <> "" Then
                cell.Locked = True
            End If
        End If
    Next cell
    Me.Protect
End Sub
      

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Me.ProtectContents Then
        Me.Unprotect ("SecretPassword")
        For Each cell In Target
            cell.Locked = True
        Next cell
        Me.Protect ("SecretPassword")
    End If
End Sub
      

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.