Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatically Protecting After Input.
by Allen Wyatt
(last updated November 13, 2018)
Excel offers protection for your worksheets, meaning that you can protect the contents of cells so they cannot be changed. Exactly how you use this protection has been discussed in other issues of ExcelTips.
hat if you want to allow cells to be edited, but you want them to become protected right after someone enters information in the cell? For instance, you have cells in which a user could enter information, but once entered, you don't want them to have the ability to change the information they entered.
There is no inherent ability in Excel to protect your input after entry, but you can create the ability through the use of a macro. The following macro is an example of how you can do this:
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Intersect(Range("A1:D100"), Target) If Not MyRange Is Nothing Then Sheets("Sheet1").Unprotect password:="hello" MyRange.Locked = True Sheets("Sheet1").Protect password:="hello" End If End Sub
This macro assumes that the worksheet has already been protected and that all the cells where you want input to be possible are unlocked. What it does is check to see if the input was done in the proper range of cells, in this case somewhere in the range of A1:D100. If it was, then the worksheet is unprotected, the cell in which information was just entered is locked, and the worksheet is again protected.
If you are using this approach in your own workbook, you will need to modify the potential input range and you will want to change the password used to unprotect and protect the worksheet.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10203) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Automatically Protecting After Input.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When you protect a worksheet, one of the benefits is that you can limit which cells can be used for data entry. How a ...Discover More
When you share a protected workbook with other people, you may not want them to get around the protection by creating a ...Discover More
Excel allows you to protect your worksheets so they can only be changed as you want to have happen. If you unprotect a ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.