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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Corey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Unprotected.
>
>
> "Jon Peltier" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>> _______
>>
>>
>> "Corey" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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....
>>>
>>
>>
>
>
Similar Threads
