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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlocking cells
Hi Gord
Changed routine as you suggested (I commented out the first bit of Ivan's code - Yes???) Routine still stops with same error when col D = "Mileage" but does not stop when col D < "Mileage". In both cases the cursor moves to next row Col B. In both cases if you go back to the previous row the correct cells have been unlocked and the cursor now moves to them. (It is just as if the unlock didn't happen quickly enough to allow the cursor on to the newly unlocked cell OR the next cell to get the focus is held somewhere and that value needs changing in the code) The resetting of the values when D changes from "Mileage" to non-"Mileage" or vice versa does not now work nor does it work if D is deleted. However I have played about with so many options now that I am beginning to wonder whether my statement regarding Ivan's original code was in fact true. I am going back to check on that. Regards "Gord Dibben" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlocking cells
Hi Ivan and Gord
Have checked Ivan's original code and it does work correctly when the sheet is unprotected. "johnsail" wrote: Hi Gord Changed routine as you suggested (I commented out the first bit of Ivan's code - Yes???) Routine still stops with same error when col D = "Mileage" but does not stop when col D < "Mileage". In both cases the cursor moves to next row Col B. In both cases if you go back to the previous row the correct cells have been unlocked and the cursor now moves to them. (It is just as if the unlock didn't happen quickly enough to allow the cursor on to the newly unlocked cell OR the next cell to get the focus is held somewhere and that value needs changing in the code) The resetting of the values when D changes from "Mileage" to non-"Mileage" or vice versa does not now work nor does it work if D is deleted. However I have played about with so many options now that I am beginning to wonder whether my statement regarding Ivan's original code was in fact true. I am going back to check on that. Regards "Gord Dibben" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlocking cells
Hi Ivan and Gord
I realise that I have not mentionbed Version of Excel which IS Excel 2003 SP3. Could this have anything to do with the problem? Just a thought. Regards "johnsail" wrote: Hi Ivan and Gord Have checked Ivan's original code and it does work correctly when the sheet is unprotected. "johnsail" wrote: Hi Gord Changed routine as you suggested (I commented out the first bit of Ivan's code - Yes???) Routine still stops with same error when col D = "Mileage" but does not stop when col D < "Mileage". In both cases the cursor moves to next row Col B. In both cases if you go back to the previous row the correct cells have been unlocked and the cursor now moves to them. (It is just as if the unlock didn't happen quickly enough to allow the cursor on to the newly unlocked cell OR the next cell to get the focus is held somewhere and that value needs changing in the code) The resetting of the values when D changes from "Mileage" to non-"Mileage" or vice versa does not now work nor does it work if D is deleted. However I have played about with so many options now that I am beginning to wonder whether my statement regarding Ivan's original code was in fact true. I am going back to check on that. Regards "Gord Dibben" wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlocking cells
On Apr 5, 11:23*pm, johnsail
wrote: Hi Ivan and Gord I realise that I have not mentionbed Version of Excel which IS Excel 2003 SP3. Could this have anything to do with the problem? Just a thought. Regards "johnsail" wrote: Hi Ivan and Gord Have checked Ivan's original code and it does work correctly when the sheet is unprotected. "johnsail" wrote: Hi Gord Changed routine as you suggested (I commented out the first bit of Ivan's code - Yes???) Routine still stops with same error when col D = "Mileage" but does not stop when col D < "Mileage". In both cases the cursor moves to next row Col B. In both cases if you go back to the previous row the correct cells have been unlocked and the cursor now moves to them. (It is just as if the unlock didn't happen quickly enough to allow the cursor on to the newly unlocked cell OR the next cell to get the focus is held somewhere and that value needs changing in the code) The resetting of the values when D changes from "Mileage" to non-"Mileage" or vice versa does not now work nor does it work if D is deleted. However I have played about with so many options now that I am beginning to wonder whether my statement regarding Ivan's original code was in fact true. I am going back to check on that. Regards "Gord Dibben" wrote: 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.- Hide quoted text - - Show quoted text - Hi John, I'm running Excel 2003 too, so shouldn't be a versioning issue. If it's working with the sheet unprotected, then I'd probably suggest going with Gord's suggestion of sticking the Unprotect at the start and then Protect at the end. I normally try and avoid that for the reason that if the code breaks for some obscure reason while the user is working with it, you end up with an unprotected sheet. Actually I think I have just clicked why it may not be working for you! I stuck the: ActiveSheet.Protect UserInterfaceOnly:=True line in the Worksheet_Activate event, which should fire every time the user opens the workbook and switches to that sheet. If you are pasting in the code then trying to test it straight away, the event will not have fired, since the sheet has never lost focus - i.e. it has never become active since it has been active all along. Once you have pasted, try switching to a different sheet then back and see if it works. Let me know. Cheers, Ivan. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlocking cells
Hi Ivan
Added a sheet and then moved from that sheet to the problem sheet. It asked for the sheet to be unprotected. If left protected then code stops on error 1004. If unprotected it works EXCEPT that the cursor does not move to the newly unprotected cell - it goes to the cell that it would have gone to if no entry in D was made. Can you force the focus by program? Regards PS Had extreme difficulty replying to your post - is there a limit to the number of levels in a thread? "Ivyleaf" wrote: On Apr 5, 11:23 pm, johnsail wrote: Hi Ivan and Gord I realise that I have not mentionbed Version of Excel which IS Excel 2003 SP3. Could this have anything to do with the problem? Just a thought. Regards "johnsail" wrote: Hi Ivan and Gord Have checked Ivan's original code and it does work correctly when the sheet is unprotected. "johnsail" wrote: Hi Gord Changed routine as you suggested (I commented out the first bit of Ivan's code - Yes???) Routine still stops with same error when col D = "Mileage" but does not stop when col D < "Mileage". In both cases the cursor moves to next row Col B. In both cases if you go back to the previous row the correct cells have been unlocked and the cursor now moves to them. (It is just as if the unlock didn't happen quickly enough to allow the cursor on to the newly unlocked cell OR the next cell to get the focus is held somewhere and that value needs changing in the code) The resetting of the values when D changes from "Mileage" to non-"Mileage" or vice versa does not now work nor does it work if D is deleted. However I have played about with so many options now that I am beginning to wonder whether my statement regarding Ivan's original code was in fact true. I am going back to check on that. Regards "Gord Dibben" wrote: 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.- Hide quoted text - - Show quoted text - Hi John, I'm running Excel 2003 too, so shouldn't be a versioning issue. If it's working with the sheet unprotected, then I'd probably suggest going with Gord's suggestion of sticking the Unprotect at the start and then Protect at the end. I normally try and avoid that for the reason that if the code breaks for some obscure reason while the user is working with it, you end up with an unprotected sheet. Actually I think I have just clicked why it may not be working for you! I stuck the: ActiveSheet.Protect UserInterfaceOnly:=True line in the Worksheet_Activate event, which should fire every time the user opens the workbook and switches to that sheet. If you are pasting in the code then trying to test it straight away, the event will not have fired, since the sheet has never lost focus - i.e. it has never become active since it has been active all along. Once you have pasted, try switching to a different sheet then back and see if it works. Let me know. Cheers, Ivan. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlocking cells
On Apr 6, 1:50*am, johnsail
wrote: Hi Ivan Added a sheet and then moved from that sheet to the problem sheet. It asked for the sheet to be unprotected. If left protected then code stops on error 1004. If unprotected it works EXCEPT that the cursor does not move to the newly unprotected cell - it goes to the cell that it would have gone to if no entry in D was made. Can you force the focus by program? Regards PS Had extreme difficulty replying to your post - is there a limit to the number of levels in a thread? "Ivyleaf" wrote: On Apr 5, 11:23 pm, johnsail wrote: Hi Ivan and Gord I realise that I have not mentionbed Version of Excel which IS Excel 2003 SP3. Could this have anything to do with the problem? Just a thought. Regards "johnsail" wrote: Hi Ivan and Gord Have checked Ivan's original code and it does work correctly when the sheet is unprotected. "johnsail" wrote: Hi Gord Changed routine as you suggested (I commented out the first bit of Ivan's code - Yes???) Routine still stops with same error when col D = "Mileage" but does not stop when col D < "Mileage". In both cases the cursor moves to next row Col B. In both cases if you go back to the previous row the correct cells have been unlocked and the cursor now moves to them. (It is just as if the unlock didn't happen quickly enough to allow the cursor on to the newly unlocked cell OR the next cell to get the focus is held somewhere and that value needs changing in the code) The resetting of the values when D changes from "Mileage" to non-"Mileage" or vice versa does not now work nor does it work if D is deleted. However I have played about with so many options now that I am beginning to wonder whether my statement regarding Ivan's original code was in fact true. I am going back to check on that. Regards "Gord Dibben" wrote: 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.- Hide quoted text - - Show quoted text - Hi John, I'm running Excel 2003 too, so shouldn't be a versioning issue. If it's working with the sheet unprotected, then I'd probably suggest going with Gord's suggestion of sticking the Unprotect at the start and then Protect at the end. I normally try and avoid that for the reason that if the code breaks for some obscure reason while the user is working with it, you end up with an unprotected sheet. Actually I think I have just clicked why it may not be working for you! I stuck the: ActiveSheet.Protect UserInterfaceOnly:=True line in the Worksheet_Activate event, which should fire every time the user opens the workbook and switches to that sheet. If you are pasting in the code then trying to test it straight away, the event will not have fired, since the sheet has never lost focus - i.e. it has never become active since it has been active all along. Once you have pasted, try switching to a different sheet then back and see if it works. Let me know. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi John, I didn't know you wanted the cursor to move to that cell. Easy enough to do though. Just add a line like: Target.Offset(0,1).select This will move the cursor to the cell which is on the same row, and one column across. Just change the 1 to whatever number you need. Cheers, Ivan. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlocking cells
Ivan
Thank you so much. There are a few bits to sort out re where the cursor goes during the change / delete bits but now that I know how maybe I will be able to sort by myself. But please keep watching this space. Many thanks - I was running out of hair to pull out!!!!!!! "Ivyleaf" wrote: On Apr 6, 1:50 am, johnsail wrote: Hi Ivan Added a sheet and then moved from that sheet to the problem sheet. It asked for the sheet to be unprotected. If left protected then code stops on error 1004. If unprotected it works EXCEPT that the cursor does not move to the newly unprotected cell - it goes to the cell that it would have gone to if no entry in D was made. Can you force the focus by program? Regards PS Had extreme difficulty replying to your post - is there a limit to the number of levels in a thread? "Ivyleaf" wrote: On Apr 5, 11:23 pm, johnsail wrote: Hi Ivan and Gord I realise that I have not mentionbed Version of Excel which IS Excel 2003 SP3. Could this have anything to do with the problem? Just a thought. Regards "johnsail" wrote: Hi Ivan and Gord Have checked Ivan's original code and it does work correctly when the sheet is unprotected. "johnsail" wrote: Hi Gord Changed routine as you suggested (I commented out the first bit of Ivan's code - Yes???) Routine still stops with same error when col D = "Mileage" but does not stop when col D < "Mileage". In both cases the cursor moves to next row Col B. In both cases if you go back to the previous row the correct cells have been unlocked and the cursor now moves to them. (It is just as if the unlock didn't happen quickly enough to allow the cursor on to the newly unlocked cell OR the next cell to get the focus is held somewhere and that value needs changing in the code) The resetting of the values when D changes from "Mileage" to non-"Mileage" or vice versa does not now work nor does it work if D is deleted. However I have played about with so many options now that I am beginning to wonder whether my statement regarding Ivan's original code was in fact true. I am going back to check on that. Regards "Gord Dibben" wrote: 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.- Hide quoted text - - Show quoted text - Hi John, I'm running Excel 2003 too, so shouldn't be a versioning issue. If it's working with the sheet unprotected, then I'd probably suggest going with Gord's suggestion of sticking the Unprotect at the start and then Protect at the end. I normally try and avoid that for the reason that if the code breaks for some obscure reason while the user is working with it, you end up with an unprotected sheet. Actually I think I have just clicked why it may not be working for you! I stuck the: ActiveSheet.Protect UserInterfaceOnly:=True line in the Worksheet_Activate event, which should fire every time the user opens the workbook and switches to that sheet. If you are pasting in the code then trying to test it straight away, the event will not have fired, since the sheet has never lost focus - i.e. it has never become active since it has been active all along. Once you have pasted, try switching to a different sheet then back and see if it works. Let me know. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi John, I didn't know you wanted the cursor to move to that cell. Easy enough to do though. Just add a line like: Target.Offset(0,1).select This will move the cursor to the cell which is on the same row, and one column across. Just change the 1 to whatever number you need. 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 |