Loop through sheets, deselect charts

  • Thread starter Thread starter Daniel Bonallack
  • Start date Start date
D

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
 
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
 
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
 
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
_______


.
Click to expand...
 
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...
 
Back
Top