Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection Error
I have written some code in Excel that runs a database query, updates some
sheets and charts based on that query. I call the code from a control button that first unprotects the worksheets and charts, calls the above macro and then reprotects the worksheets and charts. However, as the code copmpletes, I get an error: "The cell or chart you are trying to change is protected and therefore read-only" I know that it is happening as the last step as I have put message boxes throughout the code to find the point of error: it doesn't happen if you run it from the vba window. I have tried using the userinterfaceonly option on the protect method, but I am running Excel 2003 SP3 and it doesn't seem to recognise it. I have even tried making the final step of the macro select a cell that I have deliberately unprotected, but it doesn't work. Could the problem be that I am calling the code from a control button? I've used this before and never experienced the problem. It's driving me mad, can anyone advise? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection Error
The code from the button is:
Private Sub btnUpdateCharts_Click() ' turn off screen updating to improve speed Application.ScreenUpdating = False ' unprotect all the worksheets For Each vWorksheet In ActiveWorkbook.Worksheets sWorksheet = vWorksheet.Name ActiveWorkbook.Sheets(sWorksheet).Unprotect Password:=sPassword Next 'unprotect all the charts For Each vChart In ActiveWorkbook.Charts sChart = vChart.Name ActiveWorkbook.Charts(sChart).Unprotect Password:=sPassword Next UpdateMetrics ' calls the code to run the query 'protect the charts For Each vChart In ActiveWorkbook.Charts sChart = vChart.Name ActiveWorkbook.Charts(sChart).Protect _ Password:=sPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True Next ' protect the worksheets For Each vWorksheet In ActiveWorkbook.Sheets sWorksheet = vWorksheet.Name Select Case sWorksheet Case "Home" ActiveWorkbook.Worksheets(sWorksheet).Protect _ Password:=sPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True Case "All Audit Actions" ActiveWorkbook.Worksheets(sWorksheet).Protect _ Password:=sPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True Case Else ' do nothing as these sheets are hidden End Select Next Worksheets("Home").Range("A1").Select End Sub Cell A1 on the home worksheet is unprotected. Thanks K "Nigel" wrote: I suspect that you are trying to select or activate cells on the wrong type of object? Without seeing your code it is impossible to diagnose. Please post it. -- Regards, Nigel "KirstyW" wrote in message ... I have written some code in Excel that runs a database query, updates some sheets and charts based on that query. I call the code from a control button that first unprotects the worksheets and charts, calls the above macro and then reprotects the worksheets and charts. However, as the code copmpletes, I get an error: "The cell or chart you are trying to change is protected and therefore read-only" I know that it is happening as the last step as I have put message boxes throughout the code to find the point of error: it doesn't happen if you run it from the vba window. I have tried using the userinterfaceonly option on the protect method, but I am running Excel 2003 SP3 and it doesn't seem to recognise it. I have even tried making the final step of the macro select a cell that I have deliberately unprotected, but it doesn't work. Could the problem be that I am calling the code from a control button? I've used this before and never experienced the problem. It's driving me mad, can anyone advise? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection Error
The final line will fail unless your active sheet is 'Home'
Try using.... With Worksheets("Home") ..Activate ..Range("A1").Select End With If this fails due to protection, try placing the above code BEFORE you apply protection. -- Regards, Nigel "KirstyW" wrote in message ... The code from the button is: Private Sub btnUpdateCharts_Click() ' turn off screen updating to improve speed Application.ScreenUpdating = False ' unprotect all the worksheets For Each vWorksheet In ActiveWorkbook.Worksheets sWorksheet = vWorksheet.Name ActiveWorkbook.Sheets(sWorksheet).Unprotect Password:=sPassword Next 'unprotect all the charts For Each vChart In ActiveWorkbook.Charts sChart = vChart.Name ActiveWorkbook.Charts(sChart).Unprotect Password:=sPassword Next UpdateMetrics ' calls the code to run the query 'protect the charts For Each vChart In ActiveWorkbook.Charts sChart = vChart.Name ActiveWorkbook.Charts(sChart).Protect _ Password:=sPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True Next ' protect the worksheets For Each vWorksheet In ActiveWorkbook.Sheets sWorksheet = vWorksheet.Name Select Case sWorksheet Case "Home" ActiveWorkbook.Worksheets(sWorksheet).Protect _ Password:=sPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True Case "All Audit Actions" ActiveWorkbook.Worksheets(sWorksheet).Protect _ Password:=sPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True Case Else ' do nothing as these sheets are hidden End Select Next Worksheets("Home").Range("A1").Select End Sub Cell A1 on the home worksheet is unprotected. Thanks K "Nigel" wrote: I suspect that you are trying to select or activate cells on the wrong type of object? Without seeing your code it is impossible to diagnose. Please post it. -- Regards, Nigel "KirstyW" wrote in message ... I have written some code in Excel that runs a database query, updates some sheets and charts based on that query. I call the code from a control button that first unprotects the worksheets and charts, calls the above macro and then reprotects the worksheets and charts. However, as the code copmpletes, I get an error: "The cell or chart you are trying to change is protected and therefore read-only" I know that it is happening as the last step as I have put message boxes throughout the code to find the point of error: it doesn't happen if you run it from the vba window. I have tried using the userinterfaceonly option on the protect method, but I am running Excel 2003 SP3 and it doesn't seem to recognise it. I have even tried making the final step of the macro select a cell that I have deliberately unprotected, but it doesn't work. Could the problem be that I am calling the code from a control button? I've used this before and never experienced the problem. It's driving me mad, can anyone advise? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protection error | Excel Worksheet Functions | |||
Protection error | Excel Discussion (Misc queries) | |||
Protection Error | Excel Programming | |||
Protection Error | Excel Programming | |||
Error with protection | Excel Programming |