Multiple chart selection in Excel 2007

[Luca Brasi]

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

 

[Barb Reinhardt]

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]

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

 

[Andy Pope]

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

 

[Luca Brasi]

Andy, many thanks! Using FOR EACH works for me too!
Luca

 

[Brian Murphy]

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

 

[Jon Peltier]

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

 

[Brian Murphy]

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

 

[Andy Pope]

Brian,
I think your suggestion of naming the chart as you create it is the way to
go.

Cheers
Andy