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.
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.
Note:
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
It is not unusual, in a corporate world, to be handed a worksheet whose source you don't know. If that worksheet is ...
Discover MoreNeed to make sure that your worksheet is locked, with only the blank cells accessible to editing? You can do this easily ...
Discover MoreWhen you share a protected workbook with other people, you may not want them to get around the protection by creating a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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,
Steven
2021-07-17 04:32:50
KAY HENDERSON
Hi,
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
Prandeep
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.
thanks
2018-09-07 19:29:36
Rajesh
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,
A B C D E F
1 Date Number Text Text Number Posted
2 Date Number Text Text Number Posted
3 Date Number Text Text Number Posted
4
5
6
So Soon I type posted in column F, it should auto lock for editing C cell
2017-07-10 01:16:43
sir
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
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
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 ?
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments