Automatically Protecting After Input

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

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

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

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

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

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.

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.

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.

Really helpful, Thanks

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.

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?

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.

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

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.

Ankit

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

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

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

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

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

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

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

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

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!

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

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

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

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

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.

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 ?