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.

Automatically Protecting After Input

Written by Allen Wyatt (last updated August 29, 2022)
This tip applies to Excel 2007 and 2010


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.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Understanding Page Border Art

Add some artwork around the border of your printed page, and you may not know where that artwork comes from. You may also ...

Discover More

Changing an AutoShape

Got an AutoShape you previously added to a document, buy you now want to change to a different shape? You can change ...

Discover More

Hiding Excel in VBA

Want to have you macro completely hide the Excel interface? You can do so by using the Visible property for the Excel ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Controlling Entry Order on Unprotected Cells

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

Protecting Many Worksheets

Need to protect a lot of worksheets? Rather than protect the sheets individually, you'll appreciate the macros discussed ...

Discover More

Visually Showing a Protection Status

Need to know if a worksheet or workbook is currently protected? Excel provides some tell-tale signs, but here are some ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 9?

2023-05-30 07:35:39


Hi Allen,

I tried your solution, but it's not working as I hoped. I'm trying to create a situation where several people are working in the same Excel document. But as soon someone edit a cell(s) and save the document the cell(s) should be blocked from editing by anyone except the owner of the document.

Is this possible?

Kind regards,


2021-07-17 04:32:50


The above macro works, but I have to manually lock unlock to open/collapse groups so not working for me :(

2018-11-09 10:35:19


Sir, This code is working but if i double click in blank unlocked cell the cell automatically lock and next time didn't permission any data put the particular cell , please fix the code.

please share code if only data entry then cell was lock not double click locked.


2018-09-07 19:29:36


Hello Sir,

I want to protect a specific cell if I enter "Posted" in another cell. This is corresponding style like
e.g. if I write the word " Posted" in cell F1, then it auto lock C1
if I write the word " Posted" in cell F2, then it auto lock C2
if I write the word " Posted" in cell F3, then it auto lock C3

Whereas Column C is available for data entry, after data entry, I have to post it, then mark this entry in another cell as "Posted"
So A2 to F2,
1 Date Number Text Text Number Posted
2 Date Number Text Text Number Posted
3 Date Number Text Text Number Posted

So Soon I type posted in column F, it should auto lock for editing C cell

2017-07-10 01:16:43


I want protect the cell after save file. kindly provide any VB macro am not too much familiar in Marco.

2017-05-02 06:21:17


For anyone who might have stumbled here since this comment.
If you are having to unlock the worksheet EVERYTIME you fill in a cell then you need to first,

- Unlock the spreadsheet
- Highlight the area of the worksheet the code applies over
- Right-click and go to 'format cells'
- Go to the far right tab called 'Protection' and UNCHECK the 'Locked' section (Even if It appears as a solid fill instead of a tick)

After that you will be able to enter data in multiple cells without having to unlock everytime. Bear in mind if you need to revisit locked cells to delete or edit information you may need to repeat the steps above.

Hope this helps.

2017-04-26 19:35:05


I am new to writing macros, so this may be my fault, but I was unable to get this code to work. As I said, it is probably my fault, but could someone walk me through, step by step, on how to write this code in a new workbook, and get it to work? I need to create a passdown log of events for work, but I need to the information in the cells to be locked, and unable to be deleted, once entered, as a quality and integrity control measure. This code/macro sounds perfect for that function. Please help me out. Thank you.

2016-12-12 14:26:18


I use this code and it works. But when I unprotect to delete the content or data in that cell it keeps it protected and I can not go back and reenter data at a later time. As admin I want to delete the data and unblock cell automatically.

2016-10-15 02:12:50


Really helpful, Thanks

2016-05-23 12:12:51

Richard Seibert

I can get the cells to lock but if I enter data into one cell it locks all the cells I want it to just lock the cell the data is entered into. I don't want to have to enter the passcode 30 different times.

2016-05-09 16:25:06


Where would your 'protect after input' code go, in a module, on the sheet object or in ThisWorkbook object?

I am trying to use it on cells that have drop down list data validation assigned without very much luck. Maybe I should assign the code to a button click?

Would you have any words of wisdom?

2016-03-09 13:47:28

Sagar Rawal

I do have worksheets that automatically locks cell after i input some values but i do want to re-edit them. I haven't used any micros just a plain sheet it is. I have locked other cells that contain formula and some cells are linked with those formula cells but when i input values on other cells then it gives me output on cells containing formula. But once i input values it automatically gets locked and it really frustrating. I'd appreciate any help of yours. I'm using excel 2007 and OP is windows xp.

2016-02-06 02:40:46


Thank you very much. The information provided is very helpful.

2016-02-02 10:17:27



This is a great tip, but with this i need to unprotect the cell every time i enter the data in a cell. What is need is to give user an open cell but once he enters the data in that cell then it gets protected and same thing happens to subsequent cells as well.

2016-01-20 08:54:42

Peter Atherton


There is code below by Joenel that might do the trick!

2016-01-09 03:17:07

Ankit Gupta

My query is as below
Colum A should fetch current system date & Time & should not get changed when I reopen the workbook (autofilled by using macro code) when I enter data in colum" B".
Moreover both the colum A & B should get protected once I moved to next row.
Please provide vba code for autofilling the date & auto locking both the columns A & B.

Please .
Your reply with answer will be a gret help to me.

2015-12-24 09:23:29


Sir, This code is great!
I only have a problem with Merged Cells when I input data in merged cells it asks me to "debug" this line in the code: (MyRange.Locked = True).How to set multiple ranges for whole work book
Would be grateful for tips

2015-09-24 21:32:37

Moon Spell

thx joenel
but i dont know why it doesnt work with me

2015-09-24 21:20:22

Moon Spell

i need to lock each cell by itself after value entry directly without any effects on rest of cells
note:the sheet must be protected
some cells must be locked

2015-08-18 17:16:49


Kaede, did you figure out the answer to your question? I am trying to achieve something similar.

2015-05-13 08:44:29

dhruvit patel

user can detel data use right click option " clear content "
plz soal this problem

2015-04-03 00:04:19


Dear All,
The above written procedure is very effective but i am facing one problem that whenever i save and close the file and again opens it the above code is not working i.e. cannot auto lock the cell, Please help me.
Thanks in advance

2015-03-04 10:15:48


I doubt this is still active but... I'm not sure how to define my range, the range in the example (that I would clearly have to change to account for the information I am trying to protect) is A1:D100. How could I get this to work across 100+ sheets?

I'm trying to create a log for all suppliers and since I'll have 60+ people working within this file I want to protect the information as it's inputted but I'm not sure how to change the range to make it read across all tabs.
Could you provide some guidance?


2015-02-26 02:58:25


How the cell can be locked automatically when data is entered on that particular cells

2015-01-02 16:32:18



Here's my code.
Using my command button, how my command button lock the cell where every insert into cell a just click the command button the cell i inserted cell be lock. Please advise.

Private Sub TimeGo_Click()
With Me.TimeGo
If .Caption = "Start Time" Then
Rw = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(Rw, 1).Value = Format(Now, "hh:mm:ss")
.Caption = "End Time"

Else: .Caption = "Start Time"
Cells(Rw, 2).Value = Format(Now, "hh:mm:ss")
Cells(Rw, 3).Formula = "=mod(RC[-1]-RC[-2],1)"

End If
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:C200")) Is Nothing Then 'set your range here
ActiveSheet.Unprotect Password:="mypassword"
Target.Locked = True
ActiveSheet.Protect Password:="mypassword"
End If
End Sub

2014-11-12 01:00:20


Hello Sir.

I have a question. what if i have a command button then in every insert into cell a just click the command button then a cell i inserted cell be lock? thanks sir

2014-11-06 03:44:40


Thank you for this code it is great!
I only have a problem with merged? when I input data in merged cells it asks me to "debug" this line in the code: (MyRange.Locked = True)
Would be grateful for tips

2014-09-18 10:11:14

Glenn Case


The macro listed is an event macro which will be triggered automatically when the event (in this case a change to the worksheet) occurs. A worksheet event macro needs to go on the code page for that worksheet. To access that, just right-click the worksheet tab and select "View Code." You can then copy & paste the macro into that.

2014-09-17 10:13:18


This is what I need, however I can't get it to work. Maybe because I am not familiar enough with macros. I am not sure that my worksheet is linked to the macro properly. How is this done ? Does the worksheet need to call the macro in some way ?

This Site

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.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.