Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
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?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
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?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
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?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
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?? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
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?? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
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?? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
Thanks for replying,
Well I'm using it the same way as the other member. To allow other ppl to enter information without them deleting anything else. So lowering security isnt applicable as it will be on other ppl workstations. It will be on a server so after giving it a signature how do i make the signature apply to the other workstations when they open it. What about putting it in a trusted location, would that be a possiblity? Thanks "Gord Dibben" wrote: Lower your Security level? Or digitally sign the workbook/project so it becomes trusted and not disable macros. Gord Dibben MS Excel MVP On Thu, 2 Oct 2008 05:21:01 -0700, Locked Cells <Locked wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
If the workbook is digitally signed it will be trusted on all workstaions.
I don't work with signatures and servers so don't know if simply putting an unsigned workbook in a trusted location will do the trick. Others who deal with this issue regularly will be along to assist. Gord On Fri, 3 Oct 2008 08:38:01 -0700, Locked Cells wrote: Thanks for replying, Well I'm using it the same way as the other member. To allow other ppl to enter information without them deleting anything else. So lowering security isnt applicable as it will be on other ppl workstations. It will be on a server so after giving it a signature how do i make the signature apply to the other workstations when they open it. What about putting it in a trusted location, would that be a possiblity? Thanks "Gord Dibben" wrote: Lower your Security level? Or digitally sign the workbook/project so it becomes trusted and not disable macros. Gord Dibben MS Excel MVP On Thu, 2 Oct 2008 05:21:01 -0700, Locked Cells <Locked wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locking Cells
Thanks for the help.
"Gord Dibben" wrote: If the workbook is digitally signed it will be trusted on all workstaions. I don't work with signatures and servers so don't know if simply putting an unsigned workbook in a trusted location will do the trick. Others who deal with this issue regularly will be along to assist. Gord On Fri, 3 Oct 2008 08:38:01 -0700, Locked Cells wrote: Thanks for replying, Well I'm using it the same way as the other member. To allow other ppl to enter information without them deleting anything else. So lowering security isnt applicable as it will be on other ppl workstations. It will be on a server so after giving it a signature how do i make the signature apply to the other workstations when they open it. What about putting it in a trusted location, would that be a possiblity? Thanks "Gord Dibben" wrote: Lower your Security level? Or digitally sign the workbook/project so it becomes trusted and not disable macros. Gord Dibben MS Excel MVP On Thu, 2 Oct 2008 05:21:01 -0700, Locked Cells <Locked wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking cells | Excel Worksheet Functions | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
Locking Cells | Excel Discussion (Misc queries) | |||
locking cells | Excel Discussion (Misc queries) | |||
Locking Cells | Excel Discussion (Misc queries) |