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

by Allen Wyatt
(last updated September 10, 2014)

24

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.

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. ...

MORE FROM ALLEN

Specifying a Number of Matches

The wild card searching capabilities of Word are amazing. One thing you can do with wild cards is to specify not only a ...

Discover More

Replacing Formatting Functions as a Toggle

Sarra is having a problem getting Find and Replace to behave properly when replacing italic-formatted text. This tip provides ...

Discover More

Replacing an X with a Check Mark

In order to provide a finishing touch to your document, you may want to replace mundane X marks with fancier check marks. ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

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 ways ...

Discover More

Checking Lock Status of Cells

When you first create a worksheet, all the cells in that worksheet are formatted as locked. As you unlock various cells on ...

Discover More

Protecting Many Worksheets

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

Discover More
Subscribe

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

View most recent newsletter.

Comments

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. 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 six less than 6?

2017-05-02 06:21:17

Jezamiah

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

Wood

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

mike

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

SITESH BHUIN

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

cwaisanen

Sir,
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

Rajapakse

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


2016-02-02 10:17:27

Ajit

Hi,

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

Ankit

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.
Thanks


2015-12-24 09:23:29

kashish

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
Thanks


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

Sep

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

AMIT

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
Amit


2015-03-04 10:15:48

Kaede

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?

Thanks!


2015-02-26 02:58:25

Samir

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


2015-01-02 16:32:18

Joenel

Hi,

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

cliff

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

Wadee

Hello,
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
Thanks
Wadee


2014-09-18 10:11:14

Glenn Case

Frances:

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

Frances

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
Subscribe

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.