| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving and resizing the file window | Excel Discussion (Misc queries) | |||
| Arrow Keys Moving Window Frame instead of Moving Between Cells | Excel Discussion (Misc queries) | |||
| Sub-window not moving or maximizing? | Excel Discussion (Misc queries) | |||
| undo split window moving at the same time | Excel Discussion (Misc queries) | |||
| Moving chart window in macro | Excel Programming | |||
Linear Mode
