| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlocking cells
Hi
Have a worksheet of 40 rows and cols A to K. Only cols B,C and D are unlocked. Data is entered along each row. Within each row a) If the data entered in cell D = "999" then cell in col E should be unlocked. b) If data < "999" then cell in col J should be unlocked. c) If D is changed from "999" to something else then contents of E need to be deleted and the cell locked. d) If D is changed to "999" from any other value the contents of cell in col J need to be deleted and replaced with a formula and then locked.(The formula is held in a hidden cell K on each row). e) If the contents of cell D are deleted then both (c) and (d) above need to be actioned. Is this possible in VB? Regards John |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unlocking cells
On Apr 5, 12:34*am, johnsail
wrote: Hi Have a worksheet of 40 rows and cols A to K. Only cols B,C and D are unlocked. Data is entered along each row. Within each row a) If the data entered in cell D = "999" then cell in col E should be unlocked. b) If data < "999" then cell in col J should be unlocked. c) If D is changed from "999" to something else then contents of E need to be deleted and the cell locked. d) If D is changed to "999" from any other value the contents of cell in col J need to be deleted and replaced with a formula and then locked.(The formula is held in a hidden cell K on each row). e) If the contents of cell D are deleted then both (c) and (d) above need to be actioned. Is this possible in VB? Regards John Hi John, This may not be perfect depending on exactly how you want your criteria to behave, but it should give you a starting point: 'This is necessary to avoid having to unprotect 'and protect the sheet every time the macro fires Private Sub Worksheet_Activate() ActiveSheet.Protect UserInterfaceOnly:=True End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim OldVal, CaseE As Boolean If Target.Column = 4 Then OldVal = Target.Offset(0, 100).Value CaseE = IsEmpty(Target) If Target = 999 Then Target.Offset(0, 1).Locked = False Else Target.Offset(0, 6).Locked = False End If If (OldVal = 999 And Target < 999) Or CaseE Then With Target.Offset(0, 1) .ClearContents .Locked = True End With End If If Target < OldVal And Target = 999 Or CaseE Then With Target.Offset(0, 6) .Formula = Target.Offset(0, 7).Formula .Locked = True End With End If Target.Offset(0, 100) = Target.Value End If End Sub It should just be a case of massaging the criteria and their order until you get what you want. Note that I did have to use a 'helper' cell at Column CZ to hold the 'old value' of the cell in column D, since the worksheet doesn't have a 'before change' event. You can certainly move this closer to your data... I was just playing it safe by offsetting by 100. Cheers, Ivan. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unlocking cells
Ivan Hi
First thanks for the quick response. Have put the formula in col K and the help cell in col L. So have changed your routine to this: Private Sub Worksheet_Change(ByVal Target As Range) Dim OldVal, CaseE As Boolean If Target.Column = 4 Then OldVal = Target.Offset(0, 8).Value CaseE = IsEmpty(Target) If Target = "Mileage" Then Target.Offset(0, 1).Locked = False Else Target.Offset(0, 6).Locked = False End If If (OldVal = "Mileage" And Target < "Mileage") Or CaseE Then With Target.Offset(0, 1) .ClearContents .Locked = True End With End If If Target < OldVal And Target = "Mileage" Or CaseE Then With Target.Offset(0, 6) .Formula = Target.Offset(0, 7).Formula .Locked = True End With End If Target.Offset(0, 8) = Target.Value End If End Sub When running the code it gives the error: Run Time error 1004 Unable to set Locked property of the Range Class The debugger highlights the line: Target.Offset(0, 6).Locked = False Have I changed things incorrectly? John "Ivyleaf" wrote: On Apr 5, 12:34 am, johnsail wrote: Hi Have a worksheet of 40 rows and cols A to K. Only cols B,C and D are unlocked. Data is entered along each row. Within each row a) If the data entered in cell D = "999" then cell in col E should be unlocked. b) If data < "999" then cell in col J should be unlocked. c) If D is changed from "999" to something else then contents of E need to be deleted and the cell locked. d) If D is changed to "999" from any other value the contents of cell in col J need to be deleted and replaced with a formula and then locked.(The formula is held in a hidden cell K on each row). e) If the contents of cell D are deleted then both (c) and (d) above need to be actioned. Is this possible in VB? Regards John Hi John, This may not be perfect depending on exactly how you want your criteria to behave, but it should give you a starting point: 'This is necessary to avoid having to unprotect 'and protect the sheet every time the macro fires Private Sub Worksheet_Activate() ActiveSheet.Protect UserInterfaceOnly:=True End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim OldVal, CaseE As Boolean If Target.Column = 4 Then OldVal = Target.Offset(0, 100).Value CaseE = IsEmpty(Target) If Target = 999 Then Target.Offset(0, 1).Locked = False Else Target.Offset(0, 6).Locked = False End If If (OldVal = 999 And Target < 999) Or CaseE Then With Target.Offset(0, 1) .ClearContents .Locked = True End With End If If Target < OldVal And Target = 999 Or CaseE Then With Target.Offset(0, 6) .Formula = Target.Offset(0, 7).Formula .Locked = True End With End If Target.Offset(0, 100) = Target.Value End If End Sub It should just be a case of massaging the criteria and their order until you get what you want. Note that I did have to use a 'helper' cell at Column CZ to hold the 'old value' of the cell in column D, since the worksheet doesn't have a 'before change' event. You can certainly move this closer to your data... I was just playing it safe by offsetting by 100. Cheers, Ivan. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unlocking cells
On Apr 5, 2:32*am, johnsail
wrote: Ivan Hi First thanks for the quick response. Have put the formula in col K and the help cell in col L. So have changed your routine to this: Private Sub Worksheet_Change(ByVal Target As Range) * * Dim OldVal, CaseE As Boolean * * If Target.Column = 4 Then * * * * OldVal = Target.Offset(0, 8).Value * * * * CaseE = IsEmpty(Target) * * * * If Target = "Mileage" Then * * * * * * Target.Offset(0, 1).Locked = False * * * * * * Else * * * * * * Target.Offset(0, 6).Locked = False * * * * End If * * * * If (OldVal = "Mileage" And Target < "Mileage") Or CaseE Then * * * * * * With Target.Offset(0, 1) * * * * * * * * .ClearContents * * * * * * * * .Locked = True * * * * * * End With * * * * End If * * * * If Target < OldVal And Target = "Mileage" Or CaseE Then * * * * * * With Target.Offset(0, 6) * * * * * * * * .Formula = Target.Offset(0, 7).Formula * * * * * * * * .Locked = True * * * * * * End With * * * * End If * * * * Target.Offset(0, 8) = Target.Value * * End If End Sub When running the code it gives the error: Run Time error 1004 Unable to set Locked property of the Range Class The debugger highlights the line: Target.Offset(0, 6).Locked = False Have I changed things incorrectly? John "Ivyleaf" wrote: On Apr 5, 12:34 am, johnsail wrote: Hi Have a worksheet of 40 rows and cols A to K. Only cols B,C and D are unlocked. Data is entered along each row. Within each row a) If the data entered in cell D = "999" then cell in col E should be unlocked. b) If data < "999" then cell in col J should be unlocked. c) If D is changed from "999" to something else then contents of E need to be deleted and the cell locked. d) If D is changed to "999" from any other value the contents of cell in col J need to be deleted and replaced with a formula and then locked.(The formula is held in a hidden cell K on each row). e) If the contents of cell D are deleted then both (c) and (d) above need to be actioned. Is this possible in VB? Regards John Hi John, This may not be perfect depending on exactly how you want your criteria to behave, but it should give you a starting point: 'This is necessary to avoid having to unprotect 'and protect the sheet every time the macro fires Private Sub Worksheet_Activate() * * ActiveSheet.Protect UserInterfaceOnly:=True End Sub Private Sub Worksheet_Change(ByVal Target As Range) * * Dim OldVal, CaseE As Boolean * * If Target.Column = 4 Then * * * * OldVal = Target.Offset(0, 100).Value * * * * CaseE = IsEmpty(Target) * * * * If Target = 999 Then * * * * * * Target.Offset(0, 1).Locked = False * * * * * * Else * * * * * * Target.Offset(0, 6).Locked = False * * * * End If * * * * If (OldVal = 999 And Target < 999) Or CaseE Then * * * * * * With Target.Offset(0, 1) * * * * * * * * .ClearContents * * * * * * * * .Locked = True * * * * * * End With * * * * End If * * * * If Target < OldVal And Target = 999 Or CaseE Then * * * * * * With Target.Offset(0, 6) * * * * * * * * .Formula = Target.Offset(0, 7).Formula * * * * * * * * .Locked = True * * * * * * End With * * * * End If * * * * Target.Offset(0, 100) = Target.Value * * End If End Sub It should just be a case of massaging the criteria and their order until you get what you want. Note that I did have to use a 'helper' cell at Column CZ to hold the 'old value' of the cell in column D, since the worksheet doesn't have a 'before change' event. You can certainly move this closer to your data... I was just playing it safe by offsetting by 100. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi John, Did you stick this bit in too? 'This is necessary to avoid having to unprotect 'and protect the sheet every time the macro fires Private Sub Worksheet_Activate() ActiveSheet.Protect UserInterfaceOnly:=True End Sub As it says in the comment, unless this bit is on the sheet as well, the macro won't have permission to alter the locked cells in the sheet. You can get around it by doing a 'Protect' / 'Unprotect', but I prefer not to if I can avoid it. If you have this Sub in and you're still having problems, let me know. I didn't try your modified code, but at a glance it looked OK. Cheers, Ivan. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unlocking cells
Ivan
Yes that extra bit is in the code - I didn't alter that so I did not bother to include it in my last reply. Have done some investigating without the protection being on and it would seem that the cells are getting protected / unprotected and the clearing of cell D does wotk and does replace the formula. However with the sheet protected the code stops with the error quoted. Consequently the cursor is not allowed on to cell E for data entry (or cell J if the entry in D is not Mileage) regards John "Ivyleaf" wrote: On Apr 5, 2:32 am, johnsail wrote: Ivan Hi First thanks for the quick response. Have put the formula in col K and the help cell in col L. So have changed your routine to this: Private Sub Worksheet_Change(ByVal Target As Range) Dim OldVal, CaseE As Boolean If Target.Column = 4 Then OldVal = Target.Offset(0, 8).Value CaseE = IsEmpty(Target) If Target = "Mileage" Then Target.Offset(0, 1).Locked = False Else Target.Offset(0, 6).Locked = False End If If (OldVal = "Mileage" And Target < "Mileage") Or CaseE Then With Target.Offset(0, 1) .ClearContents .Locked = True End With End If If Target < OldVal And Target = "Mileage" Or CaseE Then With Target.Offset(0, 6) .Formula = Target.Offset(0, 7).Formula .Locked = True End With End If Target.Offset(0, 8) = Target.Value End If End Sub When running the code it gives the error: Run Time error 1004 Unable to set Locked property of the Range Class The debugger highlights the line: Target.Offset(0, 6).Locked = False Have I changed things incorrectly? John "Ivyleaf" wrote: On Apr 5, 12:34 am, johnsail wrote: Hi Have a worksheet of 40 rows and cols A to K. Only cols B,C and D are unlocked. Data is entered along each row. Within each row a) If the data entered in cell D = "999" then cell in col E should be unlocked. b) If data < "999" then cell in col J should be unlocked. c) If D is changed from "999" to something else then contents of E need to be deleted and the cell locked. d) If D is changed to "999" from any other value the contents of cell in col J need to be deleted and replaced with a formula and then locked.(The formula is held in a hidden cell K on each row). e) If the contents of cell D are deleted then both (c) and (d) above need to be actioned. Is this possible in VB? Regards John Hi John, This may not be perfect depending on exactly how you want your criteria to behave, but it should give you a starting point: 'This is necessary to avoid having to unprotect 'and protect the sheet every time the macro fires Private Sub Worksheet_Activate() ActiveSheet.Protect UserInterfaceOnly:=True End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim OldVal, CaseE As Boolean If Target.Column = 4 Then OldVal = Target.Offset(0, 100).Value CaseE = IsEmpty(Target) If Target = 999 Then Target.Offset(0, 1).Locked = False Else Target.Offset(0, 6).Locked = False End If If (OldVal = 999 And Target < 999) Or CaseE Then With Target.Offset(0, 1) .ClearContents .Locked = True End With End If If Target < OldVal And Target = 999 Or CaseE Then With Target.Offset(0, 6) .Formula = Target.Offset(0, 7).Formula .Locked = True End With End If Target.Offset(0, 100) = Target.Value End If End Sub It should just be a case of massaging the criteria and their order until you get what you want. Note that I did have to use a 'helper' cell at Column CZ to hold the 'old value' of the cell in column D, since the worksheet doesn't have a 'before change' event. You can certainly move this closer to your data... I was just playing it safe by offsetting by 100. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi John, Did you stick this bit in too? 'This is necessary to avoid having to unprotect 'and protect the sheet every time the macro fires Private Sub Worksheet_Activate() ActiveSheet.Protect UserInterfaceOnly:=True End Sub As it says in the comment, unless this bit is on the sheet as well, the macro won't have permission to alter the locked cells in the sheet. You can get around it by doing a 'Protect' / 'Unprotect', but I prefer not to if I can avoid it. If you have this Sub in and you're still having problems, let me know. I didn't try your modified code, but at a glance it looked OK. Cheers, Ivan. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
unlocking cells
What happens if you unprotect, do the work by code then re-protect.
ActiveSheet.Unprotect Password:="justme" do your stuff here ActiveSheet.Protect Password:="justme" Gord Dibben MS Excel MVP On Fri, 4 Apr 2008 15:20:00 -0700, johnsail wrote: Ivan Yes that extra bit is in the code - I didn't alter that so I did not bother to include it in my last reply. Have done some investigating without the protection being on and it would seem that the cells are getting protected / unprotected and the clearing of cell D does wotk and does replace the formula. However with the sheet protected the code stops with the error quoted. Consequently the cursor is not allowed on to cell E for data entry (or cell J if the entry in D is not Mileage) regards John "Ivyleaf" wrote: On Apr 5, 2:32 am, johnsail wrote: Ivan Hi First thanks for the quick response. Have put the formula in col K and the help cell in col L. So have changed your routine to this: Private Sub Worksheet_Change(ByVal Target As Range) Dim OldVal, CaseE As Boolean If Target.Column = 4 Then OldVal = Target.Offset(0, 8).Value CaseE = IsEmpty(Target) If Target = "Mileage" Then Target.Offset(0, 1).Locked = False Else Target.Offset(0, 6).Locked = False End If If (OldVal = "Mileage" And Target < "Mileage") Or CaseE Then With Target.Offset(0, 1) .ClearContents .Locked = True End With End If If Target < OldVal And Target = "Mileage" Or CaseE Then With Target.Offset(0, 6) .Formula = Target.Offset(0, 7).Formula .Locked = True End With End If Target.Offset(0, 8) = Target.Value End If End Sub When running the code it gives the error: Run Time error 1004 Unable to set Locked property of the Range Class The debugger highlights the line: Target.Offset(0, 6).Locked = False Have I changed things incorrectly? John "Ivyleaf" wrote: On Apr 5, 12:34 am, johnsail wrote: Hi Have a worksheet of 40 rows and cols A to K. Only cols B,C and D are unlocked. Data is entered along each row. Within each row a) If the data entered in cell D = "999" then cell in col E should be unlocked. b) If data < "999" then cell in col J should be unlocked. c) If D is changed from "999" to something else then contents of E need to be deleted and the cell locked. d) If D is changed to "999" from any other value the contents of cell in col J need to be deleted and replaced with a formula and then locked.(The formula is held in a hidden cell K on each row). e) If the contents of cell D are deleted then both (c) and (d) above need to be actioned. Is this possible in VB? Regards John Hi John, This may not be perfect depending on exactly how you want your criteria to behave, but it should give you a starting point: 'This is necessary to avoid having to unprotect 'and protect the sheet every time the macro fires Private Sub Worksheet_Activate() ActiveSheet.Protect UserInterfaceOnly:=True End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim OldVal, CaseE As Boolean If Target.Column = 4 Then OldVal = Target.Offset(0, 100).Value CaseE = IsEmpty(Target) If Target = 999 Then Target.Offset(0, 1).Locked = False Else Target.Offset(0, 6).Locked = False End If If (OldVal = 999 And Target < 999) Or CaseE Then With Target.Offset(0, 1) .ClearContents .Locked = True End With End If If Target < OldVal And Target = 999 Or CaseE Then With Target.Offset(0, 6) .Formula = Target.Offset(0, 7).Formula .Locked = True End With End If Target.Offset(0, 100) = Target.Value End If End Sub It should just be a case of massaging the criteria and their order until you get what you want. Note that I did have to use a 'helper' cell at Column CZ to hold the 'old value' of the cell in column D, since the worksheet doesn't have a 'before change' event. You can certainly move this closer to your data... I was just playing it safe by offsetting by 100. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi John, Did you stick this bit in too? 'This is necessary to avoid having to unprotect 'and protect the sheet every time the macro fires Private Sub Worksheet_Activate() ActiveSheet.Protect UserInterfaceOnly:=True End Sub As it says in the comment, unless this bit is on the sheet as well, the macro won't have permission to alter the locked cells in the sheet. You can get around it by doing a 'Protect' / 'Unprotect', but I prefer not to if I can avoid it. If you have this Sub in and you're still having problems, let me know. I didn't try your modified code, but at a glance it looked OK. Cheers, Ivan. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unlocking cells | Excel Discussion (Misc queries) | |||
| unlocking merge cells | Excel Programming | |||
| Unlocking cells | Excel Worksheet Functions | |||
| Unlocking Cells | Excel Programming | |||
| Unlocking Cells | Excel Programming | |||
Hybrid Mode
