Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
My add-in should process all charts the user currently has selected. Now I
ran into a problem when the user has selected several charts (but not all!) on a worksheet. It looks to me as if there is a bug in the "Selection" object in Excel 2007. Let's say I select two out of three charts on a worksheet and run the sample code below. Instead of the selected charts, the first two charts I inserted into the worksheet are processed. Am I doing something wrong or can someone confirm this is a bug in Excel 2007? Knows someone a workaround? Thanks for any hints, Luca '*** Sample code Sub Test() Dim i As Long Dim obj As Object If Windows.Count 0 Then If TypeName(ActiveSheet) = "Worksheet" Then If TypeName(ActiveWindow.Selection) = "DrawingObjects" Then For i = 1 To ActiveWindow.Selection.Count Set obj = ActiveWindow.Selection(i) If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next i End If End If End If End Sub '*** End of code |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
I prefer to avoid using SELECTION whenever possible as it slows down
execution. You may want to try something like this Dim aWS as Excel.Worksheet Dim ChtObj as Excel.ChartObject set aWS = ActiveSheet for each ChtObj in aWS.ChartObjects 'Do what you'd do. next ChtObj HTH, Barb Reinhardt "Luca Brasi" wrote: My add-in should process all charts the user currently has selected. Now I ran into a problem when the user has selected several charts (but not all!) on a worksheet. It looks to me as if there is a bug in the "Selection" object in Excel 2007. Let's say I select two out of three charts on a worksheet and run the sample code below. Instead of the selected charts, the first two charts I inserted into the worksheet are processed. Am I doing something wrong or can someone confirm this is a bug in Excel 2007? Knows someone a workaround? Thanks for any hints, Luca '*** Sample code Sub Test() Dim i As Long Dim obj As Object If Windows.Count 0 Then If TypeName(ActiveSheet) = "Worksheet" Then If TypeName(ActiveWindow.Selection) = "DrawingObjects" Then For i = 1 To ActiveWindow.Selection.Count Set obj = ActiveWindow.Selection(i) If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next i End If End If End If End Sub '*** End of code |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
Thanks Barb, I agree to this. However, for my add-in it's a requirement to
process just the currently selected charts and not all of them. There must be a way to successfully detect and process the selected charts, no? Thanks, Luc "Barb Reinhardt" wrote: I prefer to avoid using SELECTION whenever possible as it slows down execution. You may want to try something like this Dim aWS as Excel.Worksheet Dim ChtObj as Excel.ChartObject set aWS = ActiveSheet for each ChtObj in aWS.ChartObjects 'Do what you'd do. next ChtObj HTH, Barb Reinhardt "Luca Brasi" wrote: My add-in should process all charts the user currently has selected. Now I ran into a problem when the user has selected several charts (but not all!) on a worksheet. It looks to me as if there is a bug in the "Selection" object in Excel 2007. Let's say I select two out of three charts on a worksheet and run the sample code below. Instead of the selected charts, the first two charts I inserted into the worksheet are processed. Am I doing something wrong or can someone confirm this is a bug in Excel 2007? Knows someone a workaround? Thanks for any hints, Luca '*** Sample code Sub Test() Dim i As Long Dim obj As Object If Windows.Count 0 Then If TypeName(ActiveSheet) = "Worksheet" Then If TypeName(ActiveWindow.Selection) = "DrawingObjects" Then For i = 1 To ActiveWindow.Selection.Count Set obj = ActiveWindow.Selection(i) If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next i End If End If End If End Sub '*** End of code |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
Hi,
Looks like a bug with VBA and the OM. If you step through your code and add ActiveWindow.Selection into the Watch Window, you can see the correct names of the chart objects under each Item. But using the code to access it returns different information. This revised code worked for me. Sub Test() Dim obj As Object If Not ActiveChart Is Nothing Then ' toggle legend to show which charts are processed ActiveChart.SetElement IIf(ActiveChart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) Else For Each obj In Selection If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next End If End Sub Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Luca Brasi" wrote in message ... My add-in should process all charts the user currently has selected. Now I ran into a problem when the user has selected several charts (but not all!) on a worksheet. It looks to me as if there is a bug in the "Selection" object in Excel 2007. Let's say I select two out of three charts on a worksheet and run the sample code below. Instead of the selected charts, the first two charts I inserted into the worksheet are processed. Am I doing something wrong or can someone confirm this is a bug in Excel 2007? Knows someone a workaround? Thanks for any hints, Luca '*** Sample code Sub Test() Dim i As Long Dim obj As Object If Windows.Count 0 Then If TypeName(ActiveSheet) = "Worksheet" Then If TypeName(ActiveWindow.Selection) = "DrawingObjects" Then For i = 1 To ActiveWindow.Selection.Count Set obj = ActiveWindow.Selection(i) If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next i End If End If End If End Sub '*** End of code |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
Andy, many thanks! Using FOR EACH works for me too!
Luca "Andy Pope" wrote: Hi, Looks like a bug with VBA and the OM. If you step through your code and add ActiveWindow.Selection into the Watch Window, you can see the correct names of the chart objects under each Item. But using the code to access it returns different information. This revised code worked for me. Sub Test() Dim obj As Object If Not ActiveChart Is Nothing Then ' toggle legend to show which charts are processed ActiveChart.SetElement IIf(ActiveChart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) Else For Each obj In Selection If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next End If End Sub Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Luca Brasi" wrote in message ... My add-in should process all charts the user currently has selected. Now I ran into a problem when the user has selected several charts (but not all!) on a worksheet. It looks to me as if there is a bug in the "Selection" object in Excel 2007. Let's say I select two out of three charts on a worksheet and run the sample code below. Instead of the selected charts, the first two charts I inserted into the worksheet are processed. Am I doing something wrong or can someone confirm this is a bug in Excel 2007? Knows someone a workaround? Thanks for any hints, Luca '*** Sample code Sub Test() Dim i As Long Dim obj As Object If Windows.Count 0 Then If TypeName(ActiveSheet) = "Worksheet" Then If TypeName(ActiveWindow.Selection) = "DrawingObjects" Then For i = 1 To ActiveWindow.Selection.Count Set obj = ActiveWindow.Selection(i) If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next i End If End If End If End Sub '*** End of code |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
This is brilliant, Andy!
I've been looking a long time for a simple way to operate on a selected subset of charts in Excel 2007. Cheers, Brian On Sep 28, 7:17*am, "Andy Pope" wrote: Hi, Looks like a bug with VBA and the OM. If you step through your code and add ActiveWindow.Selection into the Watch Window, you can see the correct names of the chart objects under each Item. But using the code to access it returns different information. This revised code worked for me. Sub Test() * * Dim obj As Object * * If Not ActiveChart Is Nothing Then * * * * * * ' toggle legend to show which charts are processed * * * * ActiveChart.SetElement IIf(ActiveChart.HasLegend, _ * * * * * * * * msoElementLegendNone, msoElementLegendBottom) * * Else * * * * For Each obj In Selection * * * * * * If TypeName(obj) = "ChartObject" Then * * * * * * * * ' toggle legend to show which charts are processed * * * * * * * * obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ * * * * * * * * * * msoElementLegendNone, msoElementLegendBottom) * * * * * * End If * * * * Next * * End If End Sub Cheers Andy -- Andy Pope, Microsoft MVP -Excelhttp://www.andypope.info"Luca Brasi" wrote in message ... My add-in should process all charts the user currently has selected. Now I ran into a problem when the user has selected several charts (but not all!) on a worksheet. It looks to me as if there is a bug in the "Selection" object inExcel 2007. Let's say I select two out of three charts on a worksheet and run the sample code below. Instead of the selected charts, the first two charts I inserted into the worksheet are processed. Am I doing something wrong or can someone confirm this is a bug inExcel 2007? Knows someone a workaround? Thanks for any hints, Luca '*** Sample code Sub Test() * *Dim i As Long * *Dim obj As Object * *If Windows.Count 0 Then * * * *If TypeName(ActiveSheet) = "Worksheet" Then * * * * * *If TypeName(ActiveWindow.Selection) = "DrawingObjects" Then * * * * * * * *For i = 1 To ActiveWindow.Selection.Count * * * * * * * * * *Set obj = ActiveWindow.Selection(i) * * * * * * * * * *If TypeName(obj) = "ChartObject" Then * * * * * * * * * * * *' toggle legend to show which charts are processed * * * * * * * * * * * *obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ * * * * * * * * * * * *msoElementLegendNone, msoElementLegendBottom) * * * * * * * * * *End If * * * * * * * *Next i * * * * * *End If * * * *End If * *End If End Sub '*** End of code |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
I haven't tried what Andy posted. The approach I've used is to add a
shape to the sheet, and make it part of the selected shape range. Having a shape as part of the selection somehow allows VBA to determine which charts are selected and which are not. So you can process your charts, then remove the shape afterwards. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Brian Murphy wrote: This is brilliant, Andy! I've been looking a long time for a simple way to operate on a selected subset of charts in Excel 2007. Cheers, Brian On Sep 28, 7:17 am, "Andy Pope" wrote: Hi, Looks like a bug with VBA and the OM. If you step through your code and add ActiveWindow.Selection into the Watch Window, you can see the correct names of the chart objects under each Item. But using the code to access it returns different information. This revised code worked for me. Sub Test() Dim obj As Object If Not ActiveChart Is Nothing Then ' toggle legend to show which charts are processed ActiveChart.SetElement IIf(ActiveChart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) Else For Each obj In Selection If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next End If End Sub Cheers Andy -- Andy Pope, Microsoft MVP -Excelhttp://www.andypope.info"Luca Brasi" wrote in message ... My add-in should process all charts the user currently has selected. Now I ran into a problem when the user has selected several charts (but not all!) on a worksheet. It looks to me as if there is a bug in the "Selection" object inExcel 2007. Let's say I select two out of three charts on a worksheet and run the sample code below. Instead of the selected charts, the first two charts I inserted into the worksheet are processed. Am I doing something wrong or can someone confirm this is a bug inExcel 2007? Knows someone a workaround? Thanks for any hints, Luca '*** Sample code Sub Test() Dim i As Long Dim obj As Object If Windows.Count 0 Then If TypeName(ActiveSheet) = "Worksheet" Then If TypeName(ActiveWindow.Selection) = "DrawingObjects" Then For i = 1 To ActiveWindow.Selection.Count Set obj = ActiveWindow.Selection(i) If TypeName(obj) = "ChartObject" Then ' toggle legend to show which charts are processed obj.Chart.SetElement IIf(obj.Chart.HasLegend, _ msoElementLegendNone, msoElementLegendBottom) End If Next i End If End If End If End Sub '*** End of code |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
Andy,
I've come across an interesting twist on this multiple chart selection story. Sometimes it works great, but sometimes not. I use code to create a series of charts on the user's worksheet by copy/pasting a chart template from a template workbook. In excel 2003 those charts would end up with .Name properties like Chart 1, Chart 2, etc. But in Excel 2007 they all take on the same name property equal to whatever the .Name property was in the template workbook. Most importantly - they all have the same .Name property if made from the same template, which is really weird. The loop does not work in this situation. In fact, I can tell something strange is happening because Excel 2007 acts rather funny when holding down the shift and/or control keys while trying to select several charts. What ends up happening is the loop acts on the same chart each time through the loop. I think I need to change my code to specifically give the pasted charts unique Name properties. Unless you can suggest a better approach. I haven't done this, yet. That's what I intend to work on next. Brian |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple chart selection in Excel 2007
Brian,
I think your suggestion of naming the chart as you create it is the way to go. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Brian Murphy" wrote in message ... Andy, I've come across an interesting twist on this multiple chart selection story. Sometimes it works great, but sometimes not. I use code to create a series of charts on the user's worksheet by copy/pasting a chart template from a template workbook. In excel 2003 those charts would end up with .Name properties like Chart 1, Chart 2, etc. But in Excel 2007 they all take on the same name property equal to whatever the .Name property was in the template workbook. Most importantly - they all have the same .Name property if made from the same template, which is really weird. The loop does not work in this situation. In fact, I can tell something strange is happening because Excel 2007 acts rather funny when holding down the shift and/or control keys while trying to select several charts. What ends up happening is the loop acts on the same chart each time through the loop. I think I need to change my code to specifically give the pasted charts unique Name properties. Unless you can suggest a better approach. I haven't done this, yet. That's what I intend to work on next. Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007: Selecting multiple objects by drawing a selection box | Excel Discussion (Misc queries) | |||
Excel 2007 Chart Data Selection- Arrow Keys Frustration | Excel Worksheet Functions | |||
2007 Excel Workbook multiple worksheets I want 1 chart | Charts and Charting in Excel | |||
excel 2007 multiple data selection colour | Excel Discussion (Misc queries) | |||
Chart Selection - Excel 2007 copy to Powerpoint 2007 | Charts and Charting in Excel |