Chart Macro Fails When Sheet Is Protected

**Philb1** · 09-16-2011, 04:48 AM

Hello
The macro to create the chart works fine all the time the sheet's unprotected. As soon as it's protected, it reports a runtime error 1004. Specified value out of range.
I've Googled trying to find an answer, but they're all as clear as mud to me.
Attached is the macro debug screen
Running Excel 2010
Thanks

**Richard Buttrey** · 09-16-2011, 04:51 AM

Hi,

Add a first line to your macro code

ActiveSheet.Unprotect

and add a last line

ActiveSheet.Protect

Regards

**Philb1** · 09-16-2011, 05:20 AM

Hi Richard, Thanks for the reply
It worked. But. The macro requires the password to unprotect the sheet, it creates the chart, pastes it onto another sheet & then protects the wrong sheet.
I would like it to not require the password & to re lock the sheet the macro started from. I've uploaded a sample for you to play with
Thanks

**Richard Buttrey** · 09-16-2011, 05:56 AM

Hi,

Try the attached

Sub Macro1()
    Dim shChart As Worksheet, oChart As Object

    Set shChart = Sheet1
    shChart.Unprotect Password:="help"
    Set oChart = shChart.Shapes.AddChart
    oChart.Select
    ActiveChart.ApplyChartTemplate ( _
                                   "C:\Users\Phil\AppData\Roaming\Microsoft\Templates\Charts\DefaultChart1.crtx")
    ActiveChart.SetSourceData Source:=Range("Charts!$B$3:$D$10")
    oChart.Cut
    With Sheets("View").Range("F3")
        .Parent.Paste
    End With

    shChart.Protect Password:="help"
End Sub

Regards

**Philb1** · 09-16-2011, 06:03 AM

Thanks for your help Richard
I came up with this

 Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Unprotect "help"
    Set MyPlotRange = Selection
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ApplyChartTemplate ( _
        "C:\Users\Phil\AppData\Roaming\Microsoft\Templates\Charts\DefaultChart1.crtx")
    ActiveChart.SetSourceData Source:=MyPlotRange
    ActiveChart.Parent.Cut
    Sheets("View").Select
    Range("F3").Select
    ActiveSheet.Paste
    Sheets("Charts").Select
    ActiveSheet.Protect "help"
    Sheets("View").Select
End Sub

It works. I'll try your solution too
Cheers