Charts.Add error '1004' Method 'Add' of object 'Sh

[Corey]

The below code was placing a chart on the activesheet by calling data from
that sheet.
But all of a sudden i now get an error.
The only thing i have changed in the workbook was a code for protecting and
unprotecting the workbook.
I have removed these codes to try to get this chart code to work, but i
cannot get rid of this error now.

Can anyone see if i have accidently altered thsi code below, or is there a
error in it ?

The line error highlighted in the CHARTS.ADD

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub CommandButton21_Click()
Application.ScreenUpdating = False
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Sheets("Utilization
Sheet").Range("T41:T42" _
), PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='Utilization Sheet'!R41C25"
ActiveChart.SeriesCollection(2).Name = "='Utilization Sheet'!R2C25"
ActiveChart.SeriesCollection(1).Values = "='Utilization Sheet'!R41C24"
ActiveChart.SeriesCollection(1).Name = "='Utilization Sheet'!R2C24"
ActiveChart.SeriesCollection(3).Values = "='Utilization Sheet'!R41C26"
ActiveChart.SeriesCollection(3).Name = "='Utilization Sheet'!R2C26"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Utilization
Sheet"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasDataTable = False
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(2).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(3).Select
Selection.BarShape = xlCylinder
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "ALL Employee's"
.Elevation = 15
.Perspective = 30
.Rotation = 40
.RightAngleAxes = False
.HeightPercent = 100
.AutoScaling = True
.ChartArea.Select
End With
Range("a1").Select
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Regards

Corey....

 

[Jon Peltier]

When you removed the workbook protection and unprotection code, did you
leave the workbook protected?

- Jon

 

[Corey]

Unprotected.

When you removed the workbook protection and unprotection code, did you
leave the workbook protected?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

 

[Jon Peltier]

In order to add a chart sheet to a workbook, the workbook must be visible
and unprotected.

At the start of your code, insert this as a check:

If ActiveWorkbook Is Nothing then
Debug.Print "NO ACTIVE WORKBOOK"
Exit Sub
ElseIf ActiveWorkbook.ProtectStructure Then
Debug.Print "ACTIVE WORKBOOK'S STRUCTURE PROTECTED"
Exit Sub
End If

I get your error message (Run-time error '1004': Method 'Add' of object
'Sheets' failed) if I try to add a chart to a protected workbook.
If there is no active workbook, the message is different (Run-time error
'1004': Method 'Charts' of object '_Global' failed)

- Jon

 

[Corey]

Thanks for the reply Jon.

Somehow i ended up getting a WorkBook protection ON.
Problem Solved.

 

[Jon Peltier]

That's what I suspected. Glad you've fixed it.

- Jon