|
Protect Data after entry
Once data is entered into a cell I need it to be protected (automatically)
and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
A formula cannot do anything but return results.
You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
Is their a way to modify this macro to work if I only want to lock 15 of the
cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
Lee
In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
Gord - I entered the code, but it does not seem to be working.
I want to be able to protect the data after it is entered into column "b"... For expamle: 1) User enters 11/13/06 in cell b2 2) Now they are in cell b3 3) They go back to cell b2 and try to enter 11/14/06 4) The entery of 11/14/06 into cell b2 is not allowed It would be nice if I was the only one that could then change the entry... I like the password option in your code. Thanks, Nick "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
Change the range A1:A15 to B1:B15
As far as only you being able to edit the range of cells I guess you would have to have the event code pull up your username and if valid, disable events so's you could do the editing. If not valid, continue with the event. I think someone not so VBA-challenged as myself would have to address that. Gord On Mon, 13 Nov 2006 14:28:02 -0800, Ndel40 wrote: Gord - I entered the code, but it does not seem to be working. I want to be able to protect the data after it is entered into column "b"... For expamle: 1) User enters 11/13/06 in cell b2 2) Now they are in cell b3 3) They go back to cell b2 and try to enter 11/14/06 4) The entery of 11/14/06 into cell b2 is not allowed It would be nice if I was the only one that could then change the entry... I like the password option in your code. Thanks, Nick "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. Gord Dibben MS Excel MVP |
Protect Data after entry
It only works once... after the first data entry the sheet remains protected.
I need to only protect the last data entry cell. For expamle: 1) User enters 11/13/06 in cell b2 1a) Protection enable for Cell b2 2) Now they are in cell b3 3) They go back to cell b2 and try to enter 11/14/06 3a) The entery of 11/14/06 into cell b2 is not allowed 4) They go to cell b3 enter 11/14/06 4a) Protection enable for Cell b3 5) Now they are in cell b4 6) They go back to cell b3 and try to enter 11/15/06 etc...... It seems like the code you posted on 8/5/06 should work, but I can't seem to get that to work either... Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Thanks, Nick "Gord Dibben" wrote: Change the range A1:A15 to B1:B15 As far as only you being able to edit the range of cells I guess you would have to have the event code pull up your username and if valid, disable events so's you could do the editing. If not valid, continue with the event. I think someone not so VBA-challenged as myself would have to address that. Gord On Mon, 13 Nov 2006 14:28:02 -0800, Ndel40 wrote: Gord - I entered the code, but it does not seem to be working. I want to be able to protect the data after it is entered into column "b"... For expamle: 1) User enters 11/13/06 in cell b2 2) Now they are in cell b3 3) They go back to cell b2 and try to enter 11/14/06 4) The entery of 11/14/06 into cell b2 is not allowed It would be nice if I was the only one that could then change the entry... I like the password option in your code. Thanks, Nick "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. Gord Dibben MS Excel MVP |
Protect Data after entry
My bad, I forgot to unlock the other cells first... It works great!
Thanks! "Ndel40" wrote: It only works once... after the first data entry the sheet remains protected. I need to only protect the last data entry cell. For expamle: 1) User enters 11/13/06 in cell b2 1a) Protection enable for Cell b2 2) Now they are in cell b3 3) They go back to cell b2 and try to enter 11/14/06 3a) The entery of 11/14/06 into cell b2 is not allowed 4) They go to cell b3 enter 11/14/06 4a) Protection enable for Cell b3 5) Now they are in cell b4 6) They go back to cell b3 and try to enter 11/15/06 etc...... It seems like the code you posted on 8/5/06 should work, but I can't seem to get that to work either... Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Thanks, Nick "Gord Dibben" wrote: Change the range A1:A15 to B1:B15 As far as only you being able to edit the range of cells I guess you would have to have the event code pull up your username and if valid, disable events so's you could do the editing. If not valid, continue with the event. I think someone not so VBA-challenged as myself would have to address that. Gord On Mon, 13 Nov 2006 14:28:02 -0800, Ndel40 wrote: Gord - I entered the code, but it does not seem to be working. I want to be able to protect the data after it is entered into column "b"... For expamle: 1) User enters 11/13/06 in cell b2 2) Now they are in cell b3 3) They go back to cell b2 and try to enter 11/14/06 4) The entery of 11/14/06 into cell b2 is not allowed It would be nice if I was the only one that could then change the entry... I like the password option in your code. Thanks, Nick "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. Gord Dibben MS Excel MVP |
Protect Data after entry
The code works great... just one problem. I can't use the autofilters after
the sheet is protected. I'm using Excel 2000 I tried adding "AutoFilter:=True", but it is not supported by Excel 2000... ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True, AutoFilter:=True Is there a way in Excel 2000 to protect the sheet and still use autofilters? "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
I have never used 2000 version so can't speak to that.
With 2002 and 2003 if you set the Autofilter columns before you protect the sheet your code should work. Try this google search return for how to employ userinterfaceonly method to allow autofiltering in Excel 2000 http://snipurl.com/14kv9 Gord On Fri, 8 Dec 2006 09:34:00 -0800, Ndel40 wrote: The code works great... just one problem. I can't use the autofilters after the sheet is protected. I'm using Excel 2000 I tried adding "AutoFilter:=True", but it is not supported by Excel 2000... ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True, AutoFilter:=True Is there a way in Excel 2000 to protect the sheet and still use autofilters? "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
It worked... I had to put it in the tab code (called the sub filters) and
call it from the sub that locked the cells. Here it is... Thanks!!! Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True Call filters End Sub Sub filters() With Worksheets("sheet1") .Protect Password:="justme", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True End With End Sub "Gord Dibben" wrote: I have never used 2000 version so can't speak to that. With 2002 and 2003 if you set the Autofilter columns before you protect the sheet your code should work. Try this google search return for how to employ userinterfaceonly method to allow autofiltering in Excel 2000 http://snipurl.com/14kv9 Gord On Fri, 8 Dec 2006 09:34:00 -0800, Ndel40 wrote: The code works great... just one problem. I can't use the autofilters after the sheet is protected. I'm using Excel 2000 I tried adding "AutoFilter:=True", but it is not supported by Excel 2000... ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True, AutoFilter:=True Is there a way in Excel 2000 to protect the sheet and still use autofilters? "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
Cleaner code... no extra sub routine required.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True With Worksheets("sheet1") .Protect Password:="justme", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True End With End Sub "Ndel40" wrote: It worked... I had to put it in the tab code (called the sub filters) and call it from the sub that locked the cells. Here it is... Thanks!!! Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True Call filters End Sub Sub filters() With Worksheets("sheet1") .Protect Password:="justme", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True End With End Sub "Gord Dibben" wrote: I have never used 2000 version so can't speak to that. With 2002 and 2003 if you set the Autofilter columns before you protect the sheet your code should work. Try this google search return for how to employ userinterfaceonly method to allow autofiltering in Excel 2000 http://snipurl.com/14kv9 Gord On Fri, 8 Dec 2006 09:34:00 -0800, Ndel40 wrote: The code works great... just one problem. I can't use the autofilters after the sheet is protected. I'm using Excel 2000 I tried adding "AutoFilter:=True", but it is not supported by Excel 2000... ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True, AutoFilter:=True Is there a way in Excel 2000 to protect the sheet and still use autofilters? "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. |
Protect Data after entry
Thanks for the feedback.
Gord On Sat, 9 Dec 2006 05:47:00 -0800, Ndel40 wrote: Cleaner code... no extra sub routine required. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True With Worksheets("sheet1") .Protect Password:="justme", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True End With End Sub "Ndel40" wrote: It worked... I had to put it in the tab code (called the sub filters) and call it from the sub that locked the cells. Here it is... Thanks!!! Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True Call filters End Sub Sub filters() With Worksheets("sheet1") .Protect Password:="justme", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True End With End Sub "Gord Dibben" wrote: I have never used 2000 version so can't speak to that. With 2002 and 2003 if you set the Autofilter columns before you protect the sheet your code should work. Try this google search return for how to employ userinterfaceonly method to allow autofiltering in Excel 2000 http://snipurl.com/14kv9 Gord On Fri, 8 Dec 2006 09:34:00 -0800, Ndel40 wrote: The code works great... just one problem. I can't use the autofilters after the sheet is protected. I'm using Excel 2000 I tried adding "AutoFilter:=True", but it is not supported by Excel 2000... ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True, AutoFilter:=True Is there a way in Excel 2000 to protect the sheet and still use autofilters? "Gord Dibben" wrote: Lee In the absence of details on which cells and column try this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A15")) Is Nothing Then ActiveSheet.Unprotect Password:="justme" With Target If .Value < "" Then .Locked = True '.Offset(0, 1).Locked = True ' to lock column B cell End If End With End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord On Fri, 10 Nov 2006 18:27:01 -0800, Lee wrote: Is their a way to modify this macro to work if I only want to lock 15 of the cells in a particular column after the data has been entered?? Thanks, "Gord Dibben" wrote: A formula cannot do anything but return results. You could use event code in the worksheet. First, select Column B and FormatCellsProtection. Uncheck "locked" If your column of receivables is other than B, edit the If Target.Cells.Column = 2 Then to something other than 2 Copy/paste the code below to the worksheet by right-click on sheet tab and "View Code" Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 2 Then ActiveSheet.Unprotect Password:="justme" N = Target.Row If Excel.Range("B" & N).Value < "" Then Excel.Range("B" & N).Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub To cap it off close the sheet module by clicking on the "x". Right-click on the workbook/project and select VBAProject Properties then "Lock project for viewing" You can password protect this module so prying eyes can't see it and obtain the password("justme"). You now save the workbook and close Excel....the VBAProject locking won't come into effect until Excel is closed out fully. If the users enable macros and if no one of them knows how to crack a VBAProject password, you should be good to go. Gord Dibben MS Excel MVP On Sat, 5 Aug 2006 12:21:01 -0700, boatsc wrote: Once data is entered into a cell I need it to be protected (automatically) and not able to be changed or deleted. I am using this for a auditing receivables and once I see the receivable on the spreadsheet, I do not want anyone to be able to change or remove it. Need to also be able to add additional receivables to spreadheet. A formula that if a cell has data it becomes protected was my thought but ???. Gord Dibben MS Excel MVP |
| All times are GMT +1. The time now is 04:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com