Moving window to show chart

[Tony]

Hi all,

I want to step through the charts embedded in a worksheet
and selectively delete some of them. My approach,
reflected in the code below, is to change the chartarea
color to something pretty garish so that I can see which
chart is which (there's 106 of them), decide if it should
be deleted, and if not, restore the chartarea color.

Here's the problem: this is a fine idea for charts I can
see (i.e., are in the visible part of the window), but it
clearly is useless if I can't see the chart. So, how do I
move the window so that the chart being operated on is in
the viewing window?


Sub DelCharts()

Dim Last As Long, i As Long
Dim CI, Ans

Last = Worksheets("Test").ChartObjects.Count

For i = Last To 1 Step -1

Application.StatusBar = "Processing Chart " & i
CI = Worksheets("Test").ChartObjects _
(i).Chart.ChartArea.Interior.ColorIndex

Worksheets("Test").ChartObjects _
(i).Chart.ChartArea.Interior.ColorIndex = 4

Ans = MsgBox("Delete?", vbYesNo)
If Ans = vbYes Then
Worksheets("Test").ChartObjects(i).Delete
Else
Worksheets("Test").ChartObjects
(i).Chart.ChartArea.Interior.ColorIndex = CI
End If
Next i

Application.StatusBar = ""

End Sub

Oh, BTW, this works as expected if run from the VBE, but
it doesn't change the chart colors if run from the
worksheet. That's not a big deal for me right now - this
is a once-off effort to solve another problem, but I'm
curious why it works in one case but not the other.

 

[Tom Ogilvy]

Dim rng as Range

For i = Last To 1 Step -1
set rng = Worksheets("Test").ChartObjects _
(i).TopeLeftCell
Application.Goto rng, True
Application.StatusBar = "Processing Chart " & i
CI = Worksheets("Test").ChartObjects _
(i).Chart.ChartArea.Interior.ColorIndex

This will put the top left corner of the chart in the top left corner of the
visible area. (or vice versa really)

 

[Robin Hammond]

If this is a one-off you might try my XspandXL chart browser as a quicker
solution than trying to write your own code. Have a look on my site.

Robin Hammond
www.enhanceddatasystems.com

 

[Guest]

Thanks Tom & Robin. Any thoughts (just out of curiosity)
why the recoloring works if I run this from this from the
VBE, but doesn't alter the chart if I run if from the
worksheet?

 

[Tom Ogilvy]

You might throw in a DoEvents

Worksheets("Test").ChartObjects _
(i).Chart.ChartArea.Interior.ColorIndex = 4
DoEvents


Make sure screenupdating is turned on.

I assume this code is in a general module and not a Sheet Module.

 

[TonyK]

Curious, DoEvents works the first time through the loop
but not thereafter.

This is in a standard module and screen updating is on,
and I'm working in XL97.

 

[Guest]

For anyone interested, I found a solution to this
question, but I don't understand why it works (or more
accurately, why the original code didn't work). If I
insert App.ScreenUpdating = True INSIDE the loop the sub
works as expected when called from the active sheet.
Putting it ahead of the loop doesn't work. And for the
record, updating was not turned off.

I guess this is a little bug in VBA.

Thanks for the help with the move window problem.

Tony