Loop through sheets, deselect charts

[Daniel Bonallack]

My workbook has approximately 100 worksheets, and about 30
of these have charts. I would like to loop through the
sheets, and make sure that cell A1 is selected.

Sub SelectA1
For i = 1 to Sheets.count
Sheets(i).visible = True
Sheets(i).select
cells(1,1).select
Next i
End Sub

The problem is, sometimes a chart is selected, and so I
get an error if I try to select cell A1.

Can anyone improve my loop to handle the cases when a
chart is selected?

Thanks in advance
Daniel Bonallack

 

[steve]

Daniel,

Change
cells(1,1).select
to
Range("A1").Select
Even though both work for me in Excel97

Another approach is to select the cell under the chart first

On Error Resume Next
Range(ActiveChart.Parent.TopLeftCell.Address).Select
on Error goto 0
Range("A1").Select

 

[Jon Peltier]

Hi Steve -

Cells(1, 1).Select
only worked in Excel 97 if the chart object was selected (by holding
Shift and selecting the chart). If the chart or a chart element was
selected, I got an error.

Range("A1").Select
worked in both cases.

I don't know why one works and the other fails.

- Jon

 

[Daniel Bonallack]

Thanks Steve/Jon - such a simple solution

-----Original Message-----
Hi Steve -

Cells(1, 1).Select
only worked in Excel 97 if the chart object was selected (by holding
Shift and selecting the chart). If the chart or a chart element was
selected, I got an error.

Range("A1").Select
worked in both cases.

I don't know why one works and the other fails.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______


.

 

[steve]

Jon,

Don't know why, either. But ran across this problem in some of my code
and resorted to Range instead of Cells when going from a chart to the
worksheet.

The Help didn't make it clear...