|
Protecting cells with VBA (Excel 2003)
In my application a user makes a choice using a dropdown box in cell k44.
Depending on the choice, I then run some code to put some data in some other cells, k59 and k61-64, which I subsequently want to protect programmatically from being changed manually. I get an error saying the program cannot set the .Locked property of the cells. I have these cells unlocked in the sheet. In the code, I unprotect the sheet before attempting to change the cells' properties. Below is the code I use. Any ideas ? Private Sub Worksheet_change(ByVal Target As Range) .... ....some Dim statements .... Application.ScreenUpdating = False Application.EnableEvents = False Sheet10.Unprotect Password:="somepwd" On Error GoTo ErrHand .... ....some other stuff .... Err = "k44" If Not Intersect(Range("k44"), Target) Is Nothing Then Select Case Range("k44").Value Case "...." Case "...." Case "Customer" Range("L51").Value = "0" Range("K51").Value = "0" Range("k59").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,2)" Range("k61").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,4)" Range("k62").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,5)" Range("k63").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,6)" Range("k64").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,7)" Range("k59").Locked = True---------jumps to ErrHand here------- Range("k61:k64").Locked = True .... ....some other stuff .... End If ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
Protecting cells with VBA (Excel 2003)
Hi
If Sheet10 is the active sheet no error should appear, but as I read your code, the macro will always execute the ErrHand code. If no errors occour you need to skip the ErrHand and goto ActiveSheet.protect etc... Snip... End If Goto ProtectSheet ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ProtectSheet: ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub Best regards, Per "nb0512" skrev i meddelelsen ... In my application a user makes a choice using a dropdown box in cell k44. Depending on the choice, I then run some code to put some data in some other cells, k59 and k61-64, which I subsequently want to protect programmatically from being changed manually. I get an error saying the program cannot set the .Locked property of the cells. I have these cells unlocked in the sheet. In the code, I unprotect the sheet before attempting to change the cells' properties. Below is the code I use. Any ideas ? Private Sub Worksheet_change(ByVal Target As Range) ... ...some Dim statements ... Application.ScreenUpdating = False Application.EnableEvents = False Sheet10.Unprotect Password:="somepwd" On Error GoTo ErrHand ... ...some other stuff ... Err = "k44" If Not Intersect(Range("k44"), Target) Is Nothing Then Select Case Range("k44").Value Case "...." Case "...." Case "Customer" Range("L51").Value = "0" Range("K51").Value = "0" Range("k59").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,2)" Range("k61").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,4)" Range("k62").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,5)" Range("k63").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,6)" Range("k64").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,7)" Range("k59").Locked = True---------jumps to ErrHand here------- Range("k61:k64").Locked = True ... ...some other stuff ... End If ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
Protecting cells with VBA (Excel 2003)
Thanks Per, my idea (see P.S.). However the error message does appear (Error
1004 if I leave out my ahem.. 'stuctured' error handling stuff). I built a small test sheet to check this protection behavior with locking and unlocking cells and it works perfectly. I suppose in the real program there is some unwanted interaction between the Excel and VBA protection mechanisms. I'll go over the Workbook code and modules again just in case... P.S. In the full code there is Err = "<cellname" above every section and Err = "" just above the ErrHand label, so if the code executes normally, the ErrHand will be skipped. If an exception occurs, the MsgBox shows the cell name where it jumped out. "Per Jessen" wrote: Hi If Sheet10 is the active sheet no error should appear, but as I read your code, the macro will always execute the ErrHand code. If no errors occour you need to skip the ErrHand and goto ActiveSheet.protect etc... Snip... End If Goto ProtectSheet ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ProtectSheet: ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub Best regards, Per "nb0512" skrev i meddelelsen ... In my application a user makes a choice using a dropdown box in cell k44. Depending on the choice, I then run some code to put some data in some other cells, k59 and k61-64, which I subsequently want to protect programmatically from being changed manually. I get an error saying the program cannot set the .Locked property of the cells. I have these cells unlocked in the sheet. In the code, I unprotect the sheet before attempting to change the cells' properties. Below is the code I use. Any ideas ? Private Sub Worksheet_change(ByVal Target As Range) ... ...some Dim statements ... Application.ScreenUpdating = False Application.EnableEvents = False Sheet10.Unprotect Password:="somepwd" On Error GoTo ErrHand ... ...some other stuff ... Err = "k44" If Not Intersect(Range("k44"), Target) Is Nothing Then Select Case Range("k44").Value Case "...." Case "...." Case "Customer" Range("L51").Value = "0" Range("K51").Value = "0" Range("k59").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,2)" Range("k61").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,4)" Range("k62").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,5)" Range("k63").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,6)" Range("k64").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,7)" Range("k59").Locked = True---------jumps to ErrHand here------- Range("k61:k64").Locked = True ... ...some other stuff ... End If ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
Protecting cells with VBA (Excel 2003)
Thanks for your reply.
I wouldn't use Err as my own variable, as Err is an object in VBA. It may disturbe your error handler. Isn't it possible to correct the code to avoid theese errors? BTW: I would use Range("A1").formula="=vlookup(.... , inserting formulas into cells. If you don't find a solution, I can take a look at it if you mail the workbook to me. Best regards, Per "nb0512" skrev i meddelelsen ... Thanks Per, my idea (see P.S.). However the error message does appear (Error 1004 if I leave out my ahem.. 'stuctured' error handling stuff). I built a small test sheet to check this protection behavior with locking and unlocking cells and it works perfectly. I suppose in the real program there is some unwanted interaction between the Excel and VBA protection mechanisms. I'll go over the Workbook code and modules again just in case... P.S. In the full code there is Err = "<cellname" above every section and Err = "" just above the ErrHand label, so if the code executes normally, the ErrHand will be skipped. If an exception occurs, the MsgBox shows the cell name where it jumped out. "Per Jessen" wrote: Hi If Sheet10 is the active sheet no error should appear, but as I read your code, the macro will always execute the ErrHand code. If no errors occour you need to skip the ErrHand and goto ActiveSheet.protect etc... Snip... End If Goto ProtectSheet ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ProtectSheet: ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub Best regards, Per "nb0512" skrev i meddelelsen ... In my application a user makes a choice using a dropdown box in cell k44. Depending on the choice, I then run some code to put some data in some other cells, k59 and k61-64, which I subsequently want to protect programmatically from being changed manually. I get an error saying the program cannot set the .Locked property of the cells. I have these cells unlocked in the sheet. In the code, I unprotect the sheet before attempting to change the cells' properties. Below is the code I use. Any ideas ? Private Sub Worksheet_change(ByVal Target As Range) ... ...some Dim statements ... Application.ScreenUpdating = False Application.EnableEvents = False Sheet10.Unprotect Password:="somepwd" On Error GoTo ErrHand ... ...some other stuff ... Err = "k44" If Not Intersect(Range("k44"), Target) Is Nothing Then Select Case Range("k44").Value Case "...." Case "...." Case "Customer" Range("L51").Value = "0" Range("K51").Value = "0" Range("k59").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,2)" Range("k61").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,4)" Range("k62").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,5)" Range("k63").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,6)" Range("k64").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,7)" Range("k59").Locked = True---------jumps to ErrHand here------- Range("k61:k64").Locked = True ... ...some other stuff ... End If ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
Protecting cells with VBA (Excel 2003)
Thanks Per, but EUREKA !!!! Tremendous cheers and boost of morale.
I knew it had to be something silly. The cells I am trying to lock are merged cells and Excel is not quite consistent in how it deals with properties of merged cells. You can change the value, interior color and a host of other properties by referring to the right most cell only without any problem, but the 'locked' property requires reference to the range occupied by the merged cells. In my case, repacing Range("k59").Locked with Range("k59:l59").Locked and so forth did the trick. Thanks for your help ! "Per Jessen" wrote: Thanks for your reply. I wouldn't use Err as my own variable, as Err is an object in VBA. It may disturbe your error handler. Isn't it possible to correct the code to avoid theese errors? BTW: I would use Range("A1").formula="=vlookup(.... , inserting formulas into cells. If you don't find a solution, I can take a look at it if you mail the workbook to me. Best regards, Per "nb0512" skrev i meddelelsen ... Thanks Per, my idea (see P.S.). However the error message does appear (Error 1004 if I leave out my ahem.. 'stuctured' error handling stuff). I built a small test sheet to check this protection behavior with locking and unlocking cells and it works perfectly. I suppose in the real program there is some unwanted interaction between the Excel and VBA protection mechanisms. I'll go over the Workbook code and modules again just in case... P.S. In the full code there is Err = "<cellname" above every section and Err = "" just above the ErrHand label, so if the code executes normally, the ErrHand will be skipped. If an exception occurs, the MsgBox shows the cell name where it jumped out. "Per Jessen" wrote: Hi If Sheet10 is the active sheet no error should appear, but as I read your code, the macro will always execute the ErrHand code. If no errors occour you need to skip the ErrHand and goto ActiveSheet.protect etc... Snip... End If Goto ProtectSheet ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ProtectSheet: ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub Best regards, Per "nb0512" skrev i meddelelsen ... In my application a user makes a choice using a dropdown box in cell k44. Depending on the choice, I then run some code to put some data in some other cells, k59 and k61-64, which I subsequently want to protect programmatically from being changed manually. I get an error saying the program cannot set the .Locked property of the cells. I have these cells unlocked in the sheet. In the code, I unprotect the sheet before attempting to change the cells' properties. Below is the code I use. Any ideas ? Private Sub Worksheet_change(ByVal Target As Range) ... ...some Dim statements ... Application.ScreenUpdating = False Application.EnableEvents = False Sheet10.Unprotect Password:="somepwd" On Error GoTo ErrHand ... ...some other stuff ... Err = "k44" If Not Intersect(Range("k44"), Target) Is Nothing Then Select Case Range("k44").Value Case "...." Case "...." Case "Customer" Range("L51").Value = "0" Range("K51").Value = "0" Range("k59").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,2)" Range("k61").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,4)" Range("k62").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,5)" Range("k63").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,6)" Range("k64").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,7)" Range("k59").Locked = True---------jumps to ErrHand here------- Range("k61:k64").Locked = True ... ...some other stuff ... End If ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
Protecting cells with VBA (Excel 2003)
Hi
Thanks for your reply. I'm glad that you solved the problem. Regards, Per "nb0512" skrev i meddelelsen ... Thanks Per, but EUREKA !!!! Tremendous cheers and boost of morale. I knew it had to be something silly. The cells I am trying to lock are merged cells and Excel is not quite consistent in how it deals with properties of merged cells. You can change the value, interior color and a host of other properties by referring to the right most cell only without any problem, but the 'locked' property requires reference to the range occupied by the merged cells. In my case, repacing Range("k59").Locked with Range("k59:l59").Locked and so forth did the trick. Thanks for your help ! "Per Jessen" wrote: Thanks for your reply. I wouldn't use Err as my own variable, as Err is an object in VBA. It may disturbe your error handler. Isn't it possible to correct the code to avoid theese errors? BTW: I would use Range("A1").formula="=vlookup(.... , inserting formulas into cells. If you don't find a solution, I can take a look at it if you mail the workbook to me. Best regards, Per "nb0512" skrev i meddelelsen ... Thanks Per, my idea (see P.S.). However the error message does appear (Error 1004 if I leave out my ahem.. 'stuctured' error handling stuff). I built a small test sheet to check this protection behavior with locking and unlocking cells and it works perfectly. I suppose in the real program there is some unwanted interaction between the Excel and VBA protection mechanisms. I'll go over the Workbook code and modules again just in case... P.S. In the full code there is Err = "<cellname" above every section and Err = "" just above the ErrHand label, so if the code executes normally, the ErrHand will be skipped. If an exception occurs, the MsgBox shows the cell name where it jumped out. "Per Jessen" wrote: Hi If Sheet10 is the active sheet no error should appear, but as I read your code, the macro will always execute the ErrHand code. If no errors occour you need to skip the ErrHand and goto ActiveSheet.protect etc... Snip... End If Goto ProtectSheet ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ProtectSheet: ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub Best regards, Per "nb0512" skrev i meddelelsen ... In my application a user makes a choice using a dropdown box in cell k44. Depending on the choice, I then run some code to put some data in some other cells, k59 and k61-64, which I subsequently want to protect programmatically from being changed manually. I get an error saying the program cannot set the .Locked property of the cells. I have these cells unlocked in the sheet. In the code, I unprotect the sheet before attempting to change the cells' properties. Below is the code I use. Any ideas ? Private Sub Worksheet_change(ByVal Target As Range) ... ...some Dim statements ... Application.ScreenUpdating = False Application.EnableEvents = False Sheet10.Unprotect Password:="somepwd" On Error GoTo ErrHand ... ...some other stuff ... Err = "k44" If Not Intersect(Range("k44"), Target) Is Nothing Then Select Case Range("k44").Value Case "...." Case "...." Case "Customer" Range("L51").Value = "0" Range("K51").Value = "0" Range("k59").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,2)" Range("k61").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,4)" Range("k62").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,5)" Range("k63").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,6)" Range("k64").Value = "=VLOOKUP($K$35,'Pop Addresses'!$A$2:$N$18,7)" Range("k59").Locked = True---------jumps to ErrHand here------- Range("k61:k64").Locked = True ... ...some other stuff ... End If ErrHand: If Err < "" Then Result = MsgBox("Error occurred excuting " & Err & " change event", vbOKOnly, "Error") End If ActiveSheet.Protect Password:="somepwd" Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
| All times are GMT +1. The time now is 06:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com