Home | Products |Purchase | FAQ | Contact Us | Useful Resources

Protect Entered Data Automatically in Excel

If you want to input data can be protected, not to be deleted. VBA procedures that are available to automatically lock the cell.

First, select the cell range, Click Ctrl+1, open the Format Cells dialog box and click Protection tab, Uncheck "Locked" options.
Then right-click on the sheet tab, select the "View Code" and paste the code at the cursor:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngMycell As Range
Me.Unprotect ("111") 'Unprotect Sheet

For Each RngMycell In Target
RngMycell.Locked = True 'Lock cells
Next

Me.Protect ("111") 'Protect Sheet
End Sub

In the code "111" is the worksheet-protection password, and can be freely modified.

How to protect cells  and cancel "Select locked cells"?

"Me.Protect ("111") 'protect Sheet" inserted before the line:
Me.EnableSelection = 1

Code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngMycell As Range
Me.Unprotect ("111") 'Unprotect Sheet

For Each RngMycell In Target
RngMycell.Locked = True 'Lock cells
Next

Me.EnableSelection = 1

Me.Protect ("111") 'Protect Sheet
End Sub

Protect data entry can add to your protection option allows setting the Format Cells this option?

My hope is that even if the data in the table are protected can also change the font color, font style, size and other

Code is as follows: (Red code that changes the font part)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngMycell As Range
Me.Unprotect ("111") 'Unprotect Sheet

For Each RngMycell In Target
RngMycell.Locked = True 'Lock cells
Next
Me.Protect Password:="111", AllowFormattingCells:=True 'Protect Sheet
End Sub