• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

LOCK AFTER ENTRY IN CELL

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Locked = True
End Sub
[/pre]
 
To elaborate on what xld said, if you want to lock every cell after data entry, then you first need to set the cell properties - protection, locked box to unchecked (false). Then, protect the worksheet. Right-click on sheet tab, view code, paste this in:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect "MyPass"   'Delete this last bit if no password used
Target.Locked = True
Me.Protect "MyPass" 'Delete this last bit if no password used
End Sub
Now, when you make a change in one of the cells that was previously unlocked, the macro will lock it to prevent further changes.
 
Dear Sir


This is ok but when i enter data in a cell then locked whole sheet ,

i want only lock a cell which entered data
 
Hi, sa.1985!

First of all, you should unlock all cells in worksheet. If you don't, when you execute the Protect method, all cells remain blocked as they're by default.

Select all cells, Ctr-1, last tab.

Regards!
 
@Istiyak

Hi!

Firstly, to xld and Luke M, they did the dirty job, I've just arrived at dessert-time.

Thank you.

Regards!
 
Dear sir,

I am very thankful to you all chandoo's team, u give help is very useful.


but second question in this , i will fill up all data in a sheet the one cell is remain , when i entry in this cell then whole sheet should be protected.


Means a1 is a cell when i entry in this cell then whole sheet will protected.
 
Hi, sa.1985!


I am confused now. Let's see if what I understood is correct.


a) you have a sheet where you enter data in several input-able cells (no objections)

b) after you enter a value in one of this cells, you want that cell not to be changed any more (objections: what if a typing error occurs?)

c) when you enter a value in the last cell available (question: A1 will always be the last?, if so, same objection as previous)

d) once you've entered values in all input-able cells any part of the sheet can be modified (no objections)


Regardless of the answer to b) and c), that is what the code posted by xld and Luke M does! So, I don't understand your second question. Could you please explain it in a different way? Thanks.


Regards!
 
Dear Sir,


your first macro's answer is for entry in cell and lock cell which is entered data.


now my question was , firstly sheet1 is unprotected and i entered data which my required in whole sheet1,


in sheet 1 only one cell (a1,b1,z1, etc) will remain for protect sheet1. one cell will be common common cell for protect sheet1, after this one cell entry sheet1 will be protected.
 
Hi, sa.1985!


Today I'm working at night and maybe that influences more than usual on my understanding capabilities, but I have to confess that I didn't understand what you explained. Sorry to say, I didn't get the point. Would you be as kind as to try again? Thanks.


Regards!
 
I'm having trouble understanding it too, but I'm going to guess this:


1) You have a range of cells in Sheet1 that require data entry

2) All cells (at least cells requiring data entry) should be unlocked initially

3) Only after the LAST cell of data is entered should the sheet be locked


You might be better off to accomplish the locking of the sheet with a button or some other trigger other than trying to determine if the last cell has been entered. That way the user can correct typing errors without difficulty first.


However if you want it this way, maybe a Worksheet_Change function that checks if every cell in the input range has values and locks the worksheet if it does?
 
Dear Sir,


I think i can't explanation which i want to tell you, so i have put a file u will find in this that what i want to say you ?


https://rapidshare.com/files/4253176341/LOCK_CELL_AFTER_ENTRY.xlsm


if you have any problem to understand u please reply me.


Thanks

Suresh.
 
I have a better understanding now. Right-click on the sheet tab and copy/paste the following:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("E12"), Target) Is Nothing Then
    If Not IsEmpty(Target.Value) Then
        Me.Protect
    End If
End If
End Sub

As soon as anything is entered in E12 the sheet will be locked. Is that what you were looking for? Do you need it to be reversible, so that clearing E12 will re-enable the sheet?
 
Dear Sir,


Thank you so much for understanding my requirement, i think your answer will be very useful me and all are visited here.


thanks again and again.


Regards

suresh
 
Dear SirBJ7,

i got ur reply at the post "Formulas display as actual formula syntax and not the results".

i guess now i am posting in the right "post". and DB means my Data Base being maintained by me in the available tables in excel..

would u please refer the above mentioned post at 4rth number by robg???

i just want each row to be protected/unprotected each time i press lock or unlock buttons. but since i have number of rows in my table, i would request you to please provide me a code which could add lock/unlock buttons automattically when i start entering the data in new row.

and sorry i dont know where to upload my sample table where you might understand my problem very clearly.

you may give me your email, i can email that if you still have not understood my problem.
 
Hi, usmanhere!

You can e-mail me to xxxxxxxxxxxxxxxx@yahoo.com.ar but if you prefer you can refer to the second green sticky post at this forums main page for guidelines about uploading.

Could you download the file I uploaded at the link in the other topic?

http://dl.dropbox.com/u/60558749/Formulas%20display%20as%20actual%20formula%20syntax%20and%20not%20the%20results%20%28for%20usmanhere%20at%20chandoo.org%29.xlsm

I think it was what you asked for. Despite of this, I'll give a look to robg's post in a while.

Regards!


EDITED: 02/08/2012 22:15 GMT-3
 
Hi, usmanhere!

I've looked at robg's posts, but they were related to a sa1985's issue. I think well shorten paths if you upload or mail your file (include expanations considered useful).

Regards!
 
Dear SirJB7

here i have uploaded the fle

http://hotfile.com/dl/149118734/a302fa9/LC_Register.xlsx.html

would u please guide me now. i have written the requirement in the file too.

and yes i have downloaded the file u uploaded.

however that does not serve my purpose.

hope u understand after downloading the file
 
Hi, usmanhere!

Here is the uploaded file: http://dl.dropbox.com/u/60558749/LOCK%20AFTER%20ENTRY%20IN%20CELL%20-%20LC%20Register%20%28for%20usmanhere%20at%20chandoo.org%29.xlsm

It's very similar to the previously uploaded, hope this serves you.

Regards!
 
dear sirbj7,

its not wat i wished.

i am sorry for it.

i will email u in detail, wat i am looking for and wat is the problem with the present sheet u uploaded.

anyways thank u very much for ur efforts
 
Hi, usmanhere!

Well, I'll wait for your mail, I thought I've taken your file and done what you asked. Maybe I misunderstood.

Regards!

(But I read and re-read the specs, and I think they're accomplished... let's wait.)
 
You must log in or register to reply here.
Back
Top