Protection Error

[KirstyW]

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?

 

[Nigel]

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.

 

[KirstyW]

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]

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
(e-mail address removed)

 

[KirstyW]

Thanks.

It didn't fix it, but it did help me highlight that my problem is with
protecting the worksheet that contains a database query. As I am not worried
about the protection on that sheet I've removed it which has solved my
immediate problem.

Thanks