The code at the bottom use to work in Excel 2003 but now in Excel 2007 I get
the error message when it executes the ActiveSheet line:

Run-time error '1004':
Application-defined or object-defined erro
---------------------------------------------------------------------------------------------
dim shtcnt as integer

For shtcnt = 2 To (ActiveWorkbook.Sheets.Count - 1)
ActiveWorkbook.Sheets(shtcnt).Select
ActiveSheet.ChartObjects("xyz chart").Selec
---------------------------------------------------------------------------------------------
I have verified that "xyz chart" exists by selecting the chart with Alt +
mouse click and seeing the name in the Name Box left of the Formula Bar. Any
ideas whats wrong?

You probably don't have a chart with that name on every sheet you're testing.
Try something like this

Dim shtcnt As Long
Dim aWB As Workbook
Dim myWS As Worksheet
Dim ChtObj As ChartObject

Set aWB = ActiveWorkbook
For shtcnt = 2 To (aWB.Sheets.Count - 1)
Set myWS = aWB.Sheets(shtcnt)
For Each ChtObj In myWS.ChartObjects
If ChtObj.Name = "xyz chart" Then
ChtObj.Select
End If

Next ChtObj
Next shtcnt

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Scooter" wrote:

> The code at the bottom use to work in Excel 2003 but now in Excel 2007 I get
> the error message when it executes the ActiveSheet line:
>
> Run-time error '1004':
> Application-defined or object-defined error
> ---------------------------------------------------------------------------------------------
> dim shtcnt as integer
>
> For shtcnt = 2 To (ActiveWorkbook.Sheets.Count - 1)
> ActiveWorkbook.Sheets(shtcnt).Select
> ActiveSheet.ChartObjects("xyz chart").Select
> ---------------------------------------------------------------------------------------------
> I have verified that "xyz chart" exists by selecting the chart with Alt +
> mouse click and seeing the name in the Name Box left of the Formula Bar. Any
> ideas whats wrong?
>

Since some of the charts are the same name in different worksheets this
probably wont work for me. I'm still puzzled why it use to work under 2003
but wont work under 2007.

"Barb Reinhardt" wrote:

> You probably don't have a chart with that name on every sheet you're testing.
> Try something like this
>
> Dim shtcnt As Long
> Dim aWB As Workbook
> Dim myWS As Worksheet
> Dim ChtObj As ChartObject
>
> Set aWB = ActiveWorkbook
> For shtcnt = 2 To (aWB.Sheets.Count - 1)
> Set myWS = aWB.Sheets(shtcnt)
> For Each ChtObj In myWS.ChartObjects
> If ChtObj.Name = "xyz chart" Then
> ChtObj.Select
> End If
>
> Next ChtObj
> Next shtcnt
>
> --
> HTH,
> Barb Reinhardt
>
> If this post was helpful to you, please click YES below.
>
>
>
> "Scooter" wrote:
>
> > The code at the bottom use to work in Excel 2003 but now in Excel 2007 I get
> > the error message when it executes the ActiveSheet line:
> >
> > Run-time error '1004':
> > Application-defined or object-defined error
> > ---------------------------------------------------------------------------------------------
> > dim shtcnt as integer
> >
> > For shtcnt = 2 To (ActiveWorkbook.Sheets.Count - 1)
> > ActiveWorkbook.Sheets(shtcnt).Select
> > ActiveSheet.ChartObjects("xyz chart").Select
> > ---------------------------------------------------------------------------------------------
> > I have verified that "xyz chart" exists by selecting the chart with Alt +
> > mouse click and seeing the name in the Name Box left of the Formula Bar. Any
> > ideas whats wrong?
> >

Have you tried it? It's looking for charts of name "xyz chart" on every
sheet between sheet count 2 and the next to last sheet.

"Scooter" wrote:

> Since some of the charts are the same name in different worksheets this
> probably wont work for me. I'm still puzzled why it use to work under 2003
> but wont work under 2007.
>
> "Barb Reinhardt" wrote:
>
> > You probably don't have a chart with that name on every sheet you're testing.
> > Try something like this
> >
> > Dim shtcnt As Long
> > Dim aWB As Workbook
> > Dim myWS As Worksheet
> > Dim ChtObj As ChartObject
> >
> > Set aWB = ActiveWorkbook
> > For shtcnt = 2 To (aWB.Sheets.Count - 1)
> > Set myWS = aWB.Sheets(shtcnt)
> > For Each ChtObj In myWS.ChartObjects
> > If ChtObj.Name = "xyz chart" Then
> > ChtObj.Select
> > End If
> >
> > Next ChtObj
> > Next shtcnt
> >
> > --
> > HTH,
> > Barb Reinhardt
> >
> > If this post was helpful to you, please click YES below.
> >
> >
> >
> > "Scooter" wrote:
> >
> > > The code at the bottom use to work in Excel 2003 but now in Excel 2007 I get
> > > the error message when it executes the ActiveSheet line:
> > >
> > > Run-time error '1004':
> > > Application-defined or object-defined error
> > > ---------------------------------------------------------------------------------------------
> > > dim shtcnt as integer
> > >
> > > For shtcnt = 2 To (ActiveWorkbook.Sheets.Count - 1)
> > > ActiveWorkbook.Sheets(shtcnt).Select
> > > ActiveSheet.ChartObjects("xyz chart").Select
> > > ---------------------------------------------------------------------------------------------
> > > I have verified that "xyz chart" exists by selecting the chart with Alt +
> > > mouse click and seeing the name in the Name Box left of the Formula Bar. Any
> > > ideas whats wrong?
> > >

I had to delete "Next ChtObj" and change "Next shtcnt" to "Next" to get it to
compile.
The line "Set myWS = aWB.Sheets(shtcnt)" doesnt appear to have worked since
after that line executed I checked the workbook and that sheet was not
selected.

I get a run-time error '91': for the code that executes after what I had
posted.


"Barb Reinhardt" wrote:

> Have you tried it? It's looking for charts of name "xyz chart" on every
> sheet between sheet count 2 and the next to last sheet.
>
> "Scooter" wrote:
>
> > Since some of the charts are the same name in different worksheets this
> > probably wont work for me. I'm still puzzled why it use to work under 2003
> > but wont work under 2007.
> >
> > "Barb Reinhardt" wrote:
> >
> > > You probably don't have a chart with that name on every sheet you're testing.
> > > Try something like this
> > >
> > > Dim shtcnt As Long
> > > Dim aWB As Workbook
> > > Dim myWS As Worksheet
> > > Dim ChtObj As ChartObject
> > >
> > > Set aWB = ActiveWorkbook
> > > For shtcnt = 2 To (aWB.Sheets.Count - 1)
> > > Set myWS = aWB.Sheets(shtcnt)
> > > For Each ChtObj In myWS.ChartObjects
> > > If ChtObj.Name = "xyz chart" Then
> > > ChtObj.Select
> > > End If
> > >
> > > Next ChtObj
> > > Next shtcnt
> > >
> > > --
> > > HTH,
> > > Barb Reinhardt
> > >
> > > If this post was helpful to you, please click YES below.
> > >
> > >
> > >
> > > "Scooter" wrote:
> > >
> > > > The code at the bottom use to work in Excel 2003 but now in Excel 2007 I get
> > > > the error message when it executes the ActiveSheet line:
> > > >
> > > > Run-time error '1004':
> > > > Application-defined or object-defined error
> > > > ---------------------------------------------------------------------------------------------
> > > > dim shtcnt as integer
> > > >
> > > > For shtcnt = 2 To (ActiveWorkbook.Sheets.Count - 1)
> > > > ActiveWorkbook.Sheets(shtcnt).Select
> > > > ActiveSheet.ChartObjects("xyz chart").Select
> > > > ---------------------------------------------------------------------------------------------
> > > > I have verified that "xyz chart" exists by selecting the chart with Alt +
> > > > mouse click and seeing the name in the Name Box left of the Formula Bar. Any
> > > > ideas whats wrong?
> > > >