I need other employees to be able to enter data such as a timestamp into a
spreadsheet. But once they enter it I want the cell to lock automatically so
they can not adjust the times later. Is there any way for the cell to lock
once data has been put in??

Use a worksheet event macro. This macro will lock A1 once data has been
entered:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ra1 = Range("A1")
If Intersect(Target, ra1) Is Nothing Then Exit Sub
ra1.Locked = True
ActiveSheet.Protect
End Sub

This assumes that we start with all cells unlocked and the worksheet
un-protected.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200789


"gpros32" wrote:

I need other employees to be able to enter data such as a timestamp into a
spreadsheet. But once they enter it I want the cell to lock automatically so
they can not adjust the times later. Is there any way for the cell to lock
once data has been put in??

What if i need multiple cells to lock once data is entered? I have a 5x30
table that needs to filled out one cell at a time and then that single cell
lock. Is is possilbe to lock each single cell from being edited once data is
inputed but still be able to input data in other cells?
thanks

"Gary''s Student" wrote:

Use a worksheet event macro. This macro will lock A1 once data has been
entered:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ra1 = Range("A1")
If Intersect(Target, ra1) Is Nothing Then Exit Sub
ra1.Locked = True
ActiveSheet.Protect
End Sub

This assumes that we start with all cells unlocked and the worksheet
un-protected.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200789


"gpros32" wrote:

I need other employees to be able to enter data such as a timestamp into a
spreadsheet. But once they enter it I want the cell to lock automatically so
they can not adjust the times later. Is there any way for the cell to lock
once data has been put in??

A slight change:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ra1 = Range("A1:E30")
If Intersect(Target, ra1) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
Target.Locked = True
ActiveSheet.Protect
End Sub

here the 5x30 is A1 thru E30. If any of these cells get data they become
locked.
--
Gary''s Student - gsnu200789


"gpros32" wrote:

What if i need multiple cells to lock once data is entered? I have a 5x30
table that needs to filled out one cell at a time and then that single cell
lock. Is is possilbe to lock each single cell from being edited once data is
inputed but still be able to input data in other cells?
thanks

"Gary''s Student" wrote:

Use a worksheet event macro. This macro will lock A1 once data has been
entered:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ra1 = Range("A1")
If Intersect(Target, ra1) Is Nothing Then Exit Sub
ra1.Locked = True
ActiveSheet.Protect
End Sub

This assumes that we start with all cells unlocked and the worksheet
un-protected.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200789


"gpros32" wrote:

I need other employees to be able to enter data such as a timestamp into a
spreadsheet. But once they enter it I want the cell to lock automatically so
they can not adjust the times later. Is there any way for the cell to lock
once data has been put in??

Thank you so much... that works for me.
you're a life saver

"Gary''s Student" wrote:

A slight change:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ra1 = Range("A1:E30")
If Intersect(Target, ra1) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
Target.Locked = True
ActiveSheet.Protect
End Sub

here the 5x30 is A1 thru E30. If any of these cells get data they become
locked.
--
Gary''s Student - gsnu200789


"gpros32" wrote:

What if i need multiple cells to lock once data is entered? I have a 5x30
table that needs to filled out one cell at a time and then that single cell
lock. Is is possilbe to lock each single cell from being edited once data is
inputed but still be able to input data in other cells?
thanks

"Gary''s Student" wrote:

Use a worksheet event macro. This macro will lock A1 once data has been
entered:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ra1 = Range("A1")
If Intersect(Target, ra1) Is Nothing Then Exit Sub
ra1.Locked = True
ActiveSheet.Protect
End Sub

This assumes that we start with all cells unlocked and the worksheet
un-protected.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200789


"gpros32" wrote:

I need other employees to be able to enter data such as a timestamp into a
spreadsheet. But once they enter it I want the cell to lock automatically so
they can not adjust the times later. Is there any way for the cell to lock
once data has been put in??

Hi this is exactly what i've been trying to do and i've got it working great.

There is one problem though when the program starts i get a bar appear
"Security Warning, Macros have been disabled [options]".

If you dont click enable then none of the security/locked cells (i.e. the
macro) does run.

Is there any way to make the program start already enabled.

Thanks

"gpros32" wrote:

Thank you so much... that works for me.
you're a life saver

"Gary''s Student" wrote:

A slight change:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ra1 = Range("A1:E30")
If Intersect(Target, ra1) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
Target.Locked = True
ActiveSheet.Protect
End Sub

here the 5x30 is A1 thru E30. If any of these cells get data they become
locked.
--
Gary''s Student - gsnu200789


"gpros32" wrote:

What if i need multiple cells to lock once data is entered? I have a 5x30
table that needs to filled out one cell at a time and then that single cell
lock. Is is possilbe to lock each single cell from being edited once data is
inputed but still be able to input data in other cells?
thanks

"Gary''s Student" wrote:

Use a worksheet event macro. This macro will lock A1 once data has been
entered:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ra1 = Range("A1")
If Intersect(Target, ra1) Is Nothing Then Exit Sub
ra1.Locked = True
ActiveSheet.Protect
End Sub

This assumes that we start with all cells unlocked and the worksheet
un-protected.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200789


"gpros32" wrote:

I need other employees to be able to enter data such as a timestamp into a
spreadsheet. But once they enter it I want the cell to lock automatically so
they can not adjust the times later. Is there any way for the cell to lock
once data has been put in??