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
|