graph /objects protection/unprotect

[BENSON]

I have a spread sheet with a linked graph, also linked to this graph is macro that adds trend lines.I wish to protect the the whole spread sheet.If I click protect work sheet and tic ( Contents, Objects,Scenarios) The macro will not work.I have tried the code active sheet unprotect but to no avail.If I protect the work sheet but only tic (Contents and Scenarios) the macros will work but obviously the graphs remain unprotected.I have enclosed the code below showing me the error line when I try the macro with all 3 options ticked

[VBA]
Sub OptionButton8_Click()
' OptionButton8_Click Macro
' Macro recorded 2013/07/10 by Peter Beckett
'
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLogarithmic, Forward _
:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Trendlines.Add(Type:=xlLogarithmic, Forward _
:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(2).Trendlines(1).Select
With Selection.Border
.ColorIndex = 46
.Weight = xlMedium
.LineStyle = xlContinuous
End With
ActiveWindow.Visible = False

Range("D18").Select
End Sub
[/VBA]

[p45cal]

check out the UserInterfaceOnly parameter in vba Help when protecting the sheet. It only protects the sheet from users, but allows vba to alter a sheet.
Record a macro of yourself protecting the sheet, then look at the code then add/change the
UserInterfaceOnly:=True
bit to it.
This only lasts as long as the sheet is open - when the sheet is closed the UserInterfaceOnly bit dies, so needs to be re-applied when the workbook is opened again; this can be done within the macro that alters the chart, or it can be done when the workbook is opened - for example, in a workbook_open event.

[BENSON]

Thanks for the guidence re interfaceopen,I placed the following code as wooksheet open event but still cant get macros to run

[VBA]
Private Sub Workbook_Open()
Dim wSheet As Worksheet


ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True


End Sub
[/VBA]

[p45cal]

It seems there just might be a bug with chart protection (there was in Excel 95 and Excel 97), so a workaround might be in order. Unprotect the sheet/chart at the beginning of the code and .Protect it afterwards.