Moving window to show chart
 

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.

Moving window to show chart
 

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)

--
Regards,
Tom Ogilvy

"Tony" wrote in message
...
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.

Moving window to show chart
 

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

"Tony" wrote in message
...
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.

Moving window to show chart
 

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?
-----Original Message-----
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)

--
Regards,
Tom Ogilvy

"Tony" wrote in
message
...
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.


.

Moving window to show chart
 

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.

--
Regards,
Tom Ogilvy

wrote in message
...
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?
-----Original Message-----
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)

--
Regards,
Tom Ogilvy

"Tony" wrote in
message
...
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.


.

Moving window to show chart
 

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.

-----Original Message-----
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.

--
Regards,
Tom Ogilvy

wrote in message
...
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?
-----Original Message-----
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)

--
Regards,
Tom Ogilvy

"Tony" wrote in
message
...
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.


.



.

Moving window to show chart
 

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


-----Original Message-----
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.

-----Original Message-----
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.

--
Regards,
Tom Ogilvy

wrote in message
...
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?
-----Original Message-----
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)

--
Regards,
Tom Ogilvy

"Tony" wrote in
message
...
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.


.



.

.