chart select causes error 1004

  • Thread starter Thread starter Scooter
  • Start date Start date
S

Scooter

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
 
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
 
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.
 
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.
 
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.
 
Back
Top