Recognizing a Chart Worksheet
 

I have a macro that performs a function on each sht of a workbook. However,
I'd like it to skip Chart sheets. How can I test that a sheet is a chart so
that an If statement will not process that sheet?

Recognizing a Chart Worksheet
 

Use an Excel.Worksheet object and ThisWorkbook.Worksheets collection.
This does not contain charts.

Another way would be using a method that charts don't have (like
Range) and testing for error - something along the lines of this:

Public Sub testForChart()
Dim wsh As Object
Dim i As Variant

For Each wsh In Excel.Sheets
On Error Resume Next
i = wsh.Range("A1").Value

If Err.Number < 0 Then
Debug.Print "Skipping chart: " & wsh.Name
Err.Clear
Else
Debug.Print wsh.Name
End If
On Error GoTo 0
Next wsh
End Sub


On Oct 30, 6:28 pm, SteveG wrote:
I have a macro that performs a function on each sht of a workbook. However,
I'd like it to skip Chart sheets. How can I test that a sheet is a chart so
that an If statement will not process that sheet?

Recognizing a Chart Worksheet
 

Declare a variable as a worksheet, then loop through the worksheets
collection:

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
etc.

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


"iliace" wrote in message
oups.com...
Use an Excel.Worksheet object and ThisWorkbook.Worksheets collection.
This does not contain charts.

Another way would be using a method that charts don't have (like
Range) and testing for error - something along the lines of this:

Public Sub testForChart()
Dim wsh As Object
Dim i As Variant

For Each wsh In Excel.Sheets
On Error Resume Next
i = wsh.Range("A1").Value

If Err.Number < 0 Then
Debug.Print "Skipping chart: " & wsh.Name
Err.Clear
Else
Debug.Print wsh.Name
End If
On Error GoTo 0
Next wsh
End Sub


On Oct 30, 6:28 pm, SteveG wrote:
I have a macro that performs a function on each sht of a workbook.
However,
I'd like it to skip Chart sheets. How can I test that a sheet is a chart
so
that an If statement will not process that sheet?

Recognizing a Chart Worksheet
 

Great, this worked well by just avoiding the chart altogether.

"Jon Peltier" wrote:

Declare a variable as a worksheet, then loop through the worksheets
collection:

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
etc.

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


"iliace" wrote in message
oups.com...
Use an Excel.Worksheet object and ThisWorkbook.Worksheets collection.
This does not contain charts.

Another way would be using a method that charts don't have (like
Range) and testing for error - something along the lines of this:

Public Sub testForChart()
Dim wsh As Object
Dim i As Variant

For Each wsh In Excel.Sheets
On Error Resume Next
i = wsh.Range("A1").Value

If Err.Number < 0 Then
Debug.Print "Skipping chart: " & wsh.Name
Err.Clear
Else
Debug.Print wsh.Name
End If
On Error GoTo 0
Next wsh
End Sub


On Oct 30, 6:28 pm, SteveG wrote:
I have a macro that performs a function on each sht of a workbook.
However,
I'd like it to skip Chart sheets. How can I test that a sheet is a chart
so
that an If statement will not process that sheet?