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?

Protection Error
 

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?

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?

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?