Free Microsoft Excel 2013 Quick Reference

Lock Cell After Data Input

I have a cell which contains a list of possible entries using data validation. I would like to have the cell be locked once the user has selected a name from the list. The sheet is protected. Any suggestions?


Post your answer or comment

comments powered by Disqus
I have an excel form that order pickers use for productivity tracking. The picker scans or enters the number of lines on an order in one cell then selects a "Start Picking" command button. We found that people delete the number of lines and the data is lost. What would the code look like for a macro to lock the cell only after data input?

Thanks!

Buddy V

"Do not create, alter or eliminate a process that enables a problem's existence!"

Hi everyone,

Thanks in advance for any help.

Worksheet is protect.
G11:G25 unlocked (blank initially)
B11:B25 locked (blank initially)
when data input at range G11:G25, macro will stamp date and time (m/d h:mm) corresponding to B11:B25. For each input at G11:G25, the cell will be locked after data input, so no changes can be done.

I noticed macro will encounter 'run time error' when try to stamp date and time at B11:B25.

How to get it done?

Regards,

warauea

Firstly, I know 0 about programming or VBA, but I found this code which auto locks cells after you enter data in them.


	VB:
	
 Excel.Range) 
     ' Place this code in the worksheet code module. ' The subroutine unprotects the sheet and IF an entry is made ' in an
empty ("") cell, the cell is locked and then the ' sheet's protection is turned back on. Any further ' attempts to edit the
cell generate the password msgbox. ' You can set the range to one cell ("A1") or an area ("A1:Z300"). ' 1. Use Format - Cells
- Protection to unlock the cells ' in the range where one time entries are to be allowed. ' 2. Protect the worksheet with the
same password as ' you use twice in the following subroutine (thepassword).
    On Error Goto justenditall 
    Application.EnableEvents = False If Not Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Value  "" Then
ActiveSheet.Unprotect Password:="thepassword" Target.Locked = True End If End If 
    ActiveSheet.Protect Password:="thepassword" 
    justenditall: Application.EnableEvents = True End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Now I want to make this only work when the file is saved. For example, when an employee opens up the file he or she is able to enter any new data, but not edit old data, and once saved, they will not be able to edit the data they just entered.
I hope this makes sense.
Thanks!

Hi

I need a macro which will lock the cells after data is entered in the cell once. Cell should become non editable and should be only edited after a password is provided.

Firstly, I know 0 about programming or VBA, but I found this code which auto locks cells after you enter data in them.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' Place this code in the worksheet code module. ' The subroutine unprotects the sheet and IF an entry is made ' in an empty ("") cell, the cell is locked and then the ' sheet's protection is turned back on. Any further ' attempts to edit the cell generate the password msgbox. ' You can set the range to one cell ("A1") or an area ("A1:Z300"). ' 1. Use Format - Cells - Protection to unlock the cells ' in the range where one time entries are to be allowed. ' 2. Protect the worksheet with the same password as ' you use twice in the following subroutine (thepassword).

On Error GoTo justenditall

Application.EnableEvents = False If Not Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Value <> "" Then ActiveSheet.Unprotect Password:="thepassword" Target.Locked = True End If End If

ActiveSheet.Protect Password:="thepassword"

justenditall: Application.EnableEvents = True End Sub

Now I want to make this only work when the file is saved, so that the users can amend mistakes. Once they are sure its al ready, they press save and it becomes locked. I want to do this so they cannot edit information from the last save.
I hope this makes sense.

Thanks!

I am trying to create an invoice sheet for my computer shop. I have never used excel really unless it was created for me.

My issue is that I want to lock a couple of cells after data is entered for example cells B13, C13, and H13 on sheet2 and so on.

I tried creating a macro

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myRange As Range
On Error Resume Next
Set myRange = Intersect(Range("C13,B13,H13"), Target)
If Not myRange Is Nothing Then
Me.Unprotect
Target.Cells.Locked = True
Me.Protect
End If

End Sub

But it then locks all after the first one is entered. How can I set them to lock after only they are filled?

I have a form that adds data to rows and only want users to be able to input data using this form. At the moment the cells can be selected and edited. If i lock rows, columns or cells the userform will not run.

Is there a way to get the form to lock the cells after the data has been added and unlock them again if the amend or delete button is used?

next cell after enter is hit

I am very computer illiterate and have searched and experimented for 2 days with limited success

here is currently what the macro does

when data is entered into a cell that cell is filled with the color green....simple...and works

also there is 3 different columns with data being entered...A....B....C....after data is entered in C it automatically puts the current date in the corresponding cell in column D....that is somewhat ok...would like it to time stamp it also

here is features that I would like to add

Once data is entered to a cell I would like to lock that data so it cannot be changed....also after data is entered into a cell on column C as I said a date is also posted in corresponding cell in column D...I would like the date/timestamp to be locked also

also to input data we will be using a bar code scanner that is programmed to hit enter after data is collected...so for example....when we scan data for column a row 2 and enter is hit(through the Bar Scanner) it goes down to row 3...instead I would like it to go to column B and same row....then after that goto column C same row...then after that go to column a but down a row

I attached the simple spreadsheet

any help would be greatly appreciated

I need a Macro or something easier, that will lock cells individually in a certain column after data has been entered. I need to prevent employees from changing project deadlines in the file.

Either that or I need the dates in one file to transfer themselves into another "closed" file to have a back up of the data in the original form. Are any or both of these things possible?

I need to lock cells that users of a spreadsheet enter data into, so they can't change the data they have entered after they save the spreadsheet. Is there any way possible that I can lock the cells with an admin password, so that I can unlock the cells for the user to re-enter data?

I have created a production data base, which many people will be using to enter the shift's production. some times it so happened that somebody manupulated other's entry. can anybody suggest a way that a cell is locked after data has been entered and if required can be edited with password.
thanx

I have a spreadsheet with up to 2000 rows which I run each day to check for matching errors. All data is general (alpah number mix).
I compare in rows 1-2000 the col A number with the col C number and the col B number with the col D number.
I am running Excel 2003.
I do not know VB or scripting.
Col A and B are automatically populated by input from a data base with up to 2000 numbers in a new worksheet.
Col C and D are populated by hand scanner input.
After scanning in the row of data into col C then col D I need to automatically return to the next empty row in col C. My scanner allows me to go from row "X" col D one cell to the right to col E (tab) or one cell down in col D (return). So I need to go left one cell or down one cell and left two cells to get back to the next empty row in col C. My scanner does not allow further commands.
I am stuck in one of these two cells after entering data in row "X" col D.

Not a complicated process. However, I want to expedite the process.
I need a way to automatically return to the next empty row of col C without hitting a key so I can scan in the next rows data.
Entering in up to 2000 rows of data in col C and D takes a lot longer without the automation I am looking for.
I built a macro but it still requires a keyed entry. I think what I need is an automatic shift/tab to move left one cell from the col D I can end up in.
Is there a way to automate the "move" or "go to" to the next empty col C row?

Other info:
In col E I compare data in col A (each row 1 - 2000) with data in col C.
In col F I compare data in col B (each row 1 - 2000) with data in col D.

The formulas for col E and F are easy. Formulas are in rows 1-2000.
=IF(A1=C1,"","BAD")
=IF(B1=D1,"","BAD")

In G1 I count the number of "BAD" compares with: =COUNTIF(E1:E2000,"BAD")
In H1 I count the number of "BAD" compares with: =COUNTIF(F1:F2000,"BAD")
In I1 I sum G1:H1 for a total of "BAD" for the day.

Help appreciated, thanks.

Is there a way to automatically protect a cell after data is imputed into the cell?

Hi
Is there a way to automatically lock a cell after a value is entered?

Thanks for the help,
Mike

I am new to this forum so hello to all.

I am trying to lock cells after today's date has passed so that no one can make changes to it after today's date has lapsed. This is for protective reasons so that people do not remove their names from reserving something after using it. Now the code should disallow locking after cell input entry when today's date hasn't passed so that changes can still be made by the user. I am trying to determine the code to do this but I have no idea as to how to do it.

Here's a scenario: I reserve something for Aprill 11, 2009. I input my name. Since it's April 9th, 2009, I am still able to make changes up and until April 11, 2009. After this date, the cell is locked and no changes can be made, except for the administrator.

Also, a code to allow the administrator to make changes within the cells if need be. That would be appreciated.

Please advise,

Thank you

Hi,

I have a worksheet where I require, that once employees enter data into cells in columns labelled 'Attendant', cells cannot be ammended thereafter. There are 3 such columns on the worksheet labelled '01 March'.
I've messed around with some code but i'm getting nowhere...attached is a sample

Thank You for Your Help

Hi Team

I require code to conditional lock cells base on input of other cells with a reset button.

Please see attach file

Thank you for your help

Andy

Hi All,

Can any one please help me with Excel VB script which locks cell after single use in shared work book.

Thanks,
Yaswanth

Hi All,

Can any one please help me with VB script which locks cell after single use in a shared work book?

Yaswanth

I would like to enter data into my spredsheet using a form, however i would
like to lock the data in the cell after the data input in order that it
cannot be edited. Is this possible?

Hello,
How can I format a cell so that is it locked after data entry? I'm trying to
set up a simple data entry form where the user cannot change their input once
they press enter (they are required to show changes in the cell below).

Thanks!
Jeff

Is there a way to lock a cell from edit or delete after a person has entered
data into it?

I am using Excel as a survey tool in a class. Each student will create a
survey and then the students will rotate around the lab and take each
student's survey. I don't want a student messing with the former student's
data by changing the answer. Therefore I need to lock or hide the answer
after the student entered it.
--
Thanks As Always
Rip

Still learning and appreciate all the help ... Have a worksheet that will be used multiple times. User will enter name and employee number only on the first instance that the worksheet is opened. Would like to protect that info automatically for further usage. Most of the worksheet is protected but some cells need to remain unlocked so the "whole" sheet protection will not work. How do I protect only specified cells, say F3, G3, H3, I3, after the user enters data thru an input box without changing any other protected cells on the sheet? New enough that I need specific help, please. Code for user input:
Private Sub Workbook_Open()
If IsEmpty(Range("F3")) Then
Cancel = True
x = InputBox("Enter your name")
Sheets("Wkly Timesheet").Range("F3") = x
End If
If IsEmpty(Range("J3")) Then
Cancel = True
x = InputBox("Enter your employee number")
Sheets("Wkly Timesheet").Range("J3") = x
End If
End Sub

Thank you for all your help - you all make me look like a genius at work for the simplest of things! :-)

Hello all,

I'm looking to create a spreadsheet that will timestamp tehn lock in manual entries once they have been made. I found an interesting use of circular referencing on http://chandoo.org/wp/2009/01/08/tim...-formula-help/ which works well.

I would now like to be able to have Excel automatically lock a cells contents after data has been initially placed in it.

For example's sack, lets say there is a column of questions in A:A. Once a question has been answered in a cell in B:B only that cell should now be locked.

The help is always appreciated!


No luck finding an answer? You could always try Google.