Is ActiveSheet a Chart 4

[Stretchwickster]

I have several sheets some of which are filled with Charts (i.e. chart on new worksheet idea), some are not. I need to check if the ActiveSheet is a chart. Is there a way to do this? Clive

 

[RobBroekhuis]

if activeworksheet.type=-4169 then
msgbox "It's a chart!"
end if

Strangely, VBA help doesn't define an Excel constant for the -4169, but it works.
Rob

 

[rmikesmith]

Clive,

Wasn't sure if you are trying to distinguish between Chart sheets and worksheets containing embedded charts. The following will identify both:

Const SheetType_Worksheet = -4167
Const SheetType_Chart = 3


Sub SheetType()
Dim sht As Object
Dim msg As String

  For Each sht In Sheets
    If sht.Type = SheetType_Worksheet Then
      msg = msg & "Sheet:  " & sht.Name & " = Worksheet" & vbCrLf
      If sht.ChartObjects.Count <> 0 Then
        msg = msg & &quot;     Contains &quot; & sht.ChartObjects.Count & &quot; embedded chart(s)&quot; & vbCrLf
      End If
    ElseIf sht.Type = SheetType_Chart Then
      msg = msg & &quot;Sheet:  &quot; & sht.Name & &quot; = Chartsheet&quot; & vbCrLf
    End If
  Next sht
  
  MsgBox msg
  
End Sub

Rob - I ran into the same issue on the constants. Notice that in my construct a chart sheet Type is 3! Go figure.


Regards,
Mike

 

[acron]

And then there is

TypeName(ActiveSheet)

A.C.

 

[RobBroekhuis]

Hallelujah! Goes to show there's always a more straightforward method Worth a star in my book.
Rob

 

[rmikesmith]

Acron,

Sweet


Mike

 

[Stretchwickster]

Mike,

I used the following code to set-up my chart:

Worksheets(MySheet).Activate
Charts.Add
ActiveChart.Name = "Results" 
With Charts("Results")
  .SetSourceData ...
  .Location Where:=xlLocationAsNewSheet
End With

I guess that by setting the location of the chart to a new sheet means that I have a Chart Sheet rather than an embedded Chart? If however I removed the .Location line in the above code would it mean I have an embedded chart? Clive

 

[Stretchwickster]

Acron - I just tried your command - just the job! Have a star from me also! Clive

 

[rmikesmith]

Clive,

Actually, from what I can tell, the Charts.Add method creates a new chart sheet while the ChartObjects.Add method creates an embedded chart. The Location method is used to move, or re-locate, a chart. So in your example, I don't believe the Location method is doing anything useful (i.e., the new chart is already on a new sheet).

HTH
Mike

 

[Stretchwickster]

By embed, do you mean "place on a sheet"? I tried a macro recording of the chart wizard and the only difference between selecting "As new sheet:" and "As object in:" is the

.Location Where:=xlLocationAsNewSheet

as opposed to

.Location Where:=xlLocationAsObject

Clive

 

[rmikesmith]

Clive,

Yes, embedded = "place on a sheet". In answering your previous questions I looked at the documentation and tried a coouple of things (but didn't use the Chart Wizard). There's more than one way to skin a cat, and the macro recorder doesn't always generate the most efficient code. If you single-step through the code in your example and stop when the yellow highlight is on the line

With Charts("Results")

then switch from the VBA editor to the Excel view, you will find that a new chart sheet named "Results" has been created. In this case, the Location method is superfluous. The Chart Wizard (or at least the macro recorder code that is generated) apparently always creates a new chart sheet then assigns the user's choice to the Location method. If you are creating a new embedded chart through code, use the ChartObjects.Add method. It gives you control over where on the worksheet to position it. Here's a very simple Example:

Sub AddEmbeddedChart()
Dim ChObj As ChartObject

  Set ChObj = Sheets("Sheet1").ChartObjects.Add(200, 100, 200, 100)
  ChObj.Chart.SetSourceData Source:=Worksheets("Sheet1").Range("B1:B6"), _
     PlotBy:=xlColumns
     
End Sub

Hope this clarifies things.

Regards,
Mike

 

[Stretchwickster]

Thanks for the clarification Mike - I understand now - hooray! A star for your patience and explanation! Clive