trouble with selecting ActiveSheet.Shapes.Range

[Brian Murphy]

I'm going nuts (again).

At the end of a routine I'm trying to select a group of embedded
charts.
According to the online help in excel 2007, the following ought to
work:

ReDim chtnames(1 to 2)
set the two chtnames
ActiveSheet.Shapes.Range(chtnames).Select

In fact, the above did work for me when I first put it in. But now...
The above generates an "the index into the specified collection is out
of bounds" run time error.

However, the following will work in the immediate window where the
above fails.
activesheet.shapes.range(array(chtnames(1),chtnames(2))).select

What am I missing?

Thanks,

Brian

 

[Rick Rothstein]

This is untested...

You ReDim'med chtnames with a lower bound of 1... do me a favor and execute
this statement in the Immediate Window...

? LBound(Array(chtnames(1),chtnames(2)))

Does this print 0 or 1 on your system? If it prints 0, then try changing
your ReDim statement to this...

ReDim chtname(0 To 1)

and then set chtname(0) and chtname(1) instead of what you set originally
and then follow that up with your last line...

ActiveSheet.Shapes.Range(chtnames).Select

 

[Peter T]

What am I missing?

Merely the fact that there's much wrong shapes and charts in 2007 as far VBA
is concerned.

That said I don't see why array(chtnames(1),chtnames(2)) works yet chtnames
fails. Yet, I think I've also had that error description before but can't
recreate at the moment.

I assume your array is chartObject string names. In all versions "name" can
be unreliable if duplicate named objects exist (shouldn't be possible but it
is), but in 2007 there seem to be even more problems using names.

ReDim arrS(1 To ActiveSheet.ChartObjects.Count) As String
ReDim arrX(1 To ActiveSheet.ChartObjects.Count) As Long

For Each shp In ActiveSheet.Shapes
If shp.Type = msoChart Then
i = i + 1
arrS(i) = shp.Name
arrX(i) = shp.ZOrderPosition
End If
Next
ActiveSheet.Shapes.Range(arrX).Select

arrS should normally work but arrX is more reliable

For your purposes adapt not to select all but as required, not sure though
it'll fix your odd problem.

Of course in earlier versions to select all charts, simply
Activesheet.Chartobjects.Select
but that doesn't work in 2007 (though other drawingobjects do work, eg
..Rectables.Select).

Regards,
Peter T

 

[Peter T]

Rick, well spotted. LBound 0/1 would appear to be the only difference
between the two arrays. However both LBound 0 and 1 arrays should work just
fine in this context, at least always has done for me AFAIK.

Brian, any difference if you change
ReDim chtnames(1 to 2)
to
ReDim chtnames(0 to 2 - 1)
' etc

It shouldn't make any difference who knows!

Regards,
Peter T

 

[Brian Murphy]

To clarify, I am selecting a subset of charts on a worksheet.

This is very old code I'm trying to get to work with excel 2007.
Originally it used a base of 0.
When that wouldn't work I tried 1.
The example in the help file specifically uses 1.

I think I'm getting bit by the .Name problems you mentioned.
I've certainly seen this problem in other forms.
Later tonight I will try the loop to get the zOrder values.
How would using Activesheet.Chartobjects compare to using .Shapes?

If you want to have some fun with .Names, try this:
Get a worksheet with a bunch of embedded charts.
Make sure they have unique .Names otherwise you'll have trouble
selecting a subset of charts.
Select some of them and run this code:

for each obj in Selection
debug.print obj.name
next
for i=1 to Selection.count
set obj = Selection(i)
debug.print obj.name
next i

What happens to me is the first loop works as expected.
The second loop starts at the front of the zOrder, I think.

Thanks,

Brian

 

[Peter T]

To clarify, I am selecting a subset of charts on a worksheet.

Already understood, though it wouldn't make any difference if embedded on
chart-sheets

This is very old code I'm trying to get to work with excel 2007.
Originally it used a base of 0.
When that wouldn't work I tried 1.
The example in the help file specifically uses 1.

As I mentioned both 0 and 1 base should work, yet 0/1 base appears to be the
only difference in what worked/failed for you.

I think I'm getting bit by the .Name problems you mentioned.
I've certainly seen this problem in other forms.
Later tonight I will try the loop to get the zOrder values.
How would using Activesheet.Chartobjects compare to using .Shapes?

ZOrder and shape.ZOrderPosition are not equivalent. Here's the ZOrder at the
drawingobjects level alternative

with activesheet.Chartobjects
arrX(1) = .Item(2).ZOrder
arrX(2) = .Item(4).ZOrder
end with
Activesheet.DRAWINGOBJECTS(arrX).Select

But with this ironically you need to be careful in earlier versions if any
Comments exist on the sheet. Their order positions are included in the mix,
unlike in 2007 where they are always last.

Another thing, in earlier versions you can do this -

ActiveSheet.ChartObjects(Array(1, 3)).Select
where 1 & 3 are the 1st & 3rd items in the chartobjects collection, but not
in 2007.

If you want to have some fun with .Names, try this:
Get a worksheet with a bunch of embedded charts.
Make sure they have unique .Names otherwise you'll have trouble
selecting a subset of charts.
Select some of them and run this code:

for each obj in Selection
debug.print obj.name
next
for i=1 to Selection.count
set obj = Selection(i)
debug.print obj.name
next i

What happens to me is the first loop works as expected.
The second loop starts at the front of the zOrder, I think.

Both work for me in the same way, but not as expected. Namely, 'Selection'
returns *all* the chartobjects even if only some are physically selected.

Part of the problem is the new chartobject is radically different to the old
chartobject.

Regards,
Peter T

 

[Brian Murphy]

Ok, here we go.

I put in the zOrderPosition stuff and it seems to work just right.

I ran into another quirk, though. Sometimes after the selecting the
shape range, not all of the charts will "look" selected, even though
they definitely are all selected (verified by hitting the Delete key
to see what is deleted). I think it might have always been the first
one in the group (don't recall which). Not life threatening, but very
annoying. After a couple of hours of fighting that battle I found
that excel is "stuck" in some goofy mode where selecting multiple
charts is working normally, but if I select the first data series on
the first chart of the group that breaks it out of this mode. So
doing this in code gets the entire selected chart group looking
selected.

Here's what I ended up with (Activechart is the last one in the group,
at this point it is "selected" but not "activated")
ActiveChart.ChartArea.Select ' doesn't work without this
line, strange but true
ActiveSheet.Shapes.Range(GetZorderArray(chtnames)
(1)).Select
ActiveChart.SeriesCollection(1).Select
ActiveSheet.Shapes.Range(GetZorderArray(chtnames)).Select

Function GetZorderArray(objnames) As Variant 'created 10/15/2009
ReDim arrx(1 To UBound(objnames) - LBound(objnames) + 1) As Long
Dim i%, shp As Shape
For i = LBound(objnames) To UBound(objnames)
For Each shp In ActiveSheet.Shapes
If shp.Type = msoChart And shp.Name = objnames(i) Then
arrx(i) = shp.ZOrderPosition
Exit For
End If
Next shp
Next i
GetZorderArray = arrx
End Function

Excel 2007 is really something else!

Brian

 

[Peter T]

Not sure I quite follow all that, you can't have an activechart and multiple
charts selected at the same time? However I know what you mean by "stuck in
some goofy mode", that can be recreated manually and once in it almost
impossible to break out of (and be able to visually show correct selection
of multiple charts).


I haven't tried the code but wonder if might want to change GetZorderArray
to something like this

Function GetZorderArray(objnames) As Variant 'created 10/15/2009
ReDim arrx(1 To UBound(objnames) - LBound(objnames) + 1) As Long
Dim i%, shp As Shape, idx as long
For i = LBound(objnames) To UBound(objnames)
For Each shp In ActiveSheet.Shapes
If shp.Type = msoChart And shp.Name = objnames(i) Then
idx = idx + 1
arrx(IDX) = shp.ZOrderPosition
Exit For
End If
Next shp
Next i
if idx then
redim preserve arrx arrx(1 To idx)
GetZorderArray = arrx
else
GetZorderArray = -1 ' nothing found ?
end if
End Function

Excel 2007 is really something else!

It's a challenge....

Regards,
Peter T

 

[Brian Murphy]

Peter,

I think I see why my post was a hard to follow. "is working normally"
was supposed to be "is not working normally"

Your suggestion is a good one. I will put in the extra logic to trap
wayward cases.

Well darn! What was working last night is not working tonight. Back
to the same monkey business with not showing all charts as being
selected even though they are. No idea why. Might be the phase of
the moon, or maybe because it's Friday.

As one might guess, right before this selecting stuff a loop operates
on each selected chart (to change the data displayed and some
formats). I just added the following immediately before the Next
statement:
ActiveChart.ChartArea.Select

It's now working, again. I'll hold my breath, keep my fingers crossed
and hope for the best.

Still have to make sure the code works in excel 2003.

Onward through the fog,

Brian

 

[Brian Murphy]

By the way. I am using groups.google.com to post to this thread.
This thread does not seem to show up at all at:
http://www.microsoft.com/communitie...lt.aspx?dg=microsoft.public.excel.programming
Is it just me, or is there some reason for this?

Brian

 

[Peter T]

Brian,

Further... The selection handles should distinguish between selection as a
chart + item or just as chartobject. But sometimes when selected as
chartobject, or multiple chartobjects, the full chart type selection handles
are shown. In this phase it can be, though not necessarily, impossible to
manually select additional chartobjects with Ctrl

Well darn! What was working last night is not working tonight.
I just added the following immediately before the Next
statement:
ActiveChart.ChartArea.Select

It's now working, again. I'll hold my breath, keep my fingers crossed
and hope for the best.

That doesn't reliably work for me. If/when you can infallibly reproduce a
workaround do post back, or if that's sometime in the future could you nudge
me off-line (address disguised in the reply-to field)

Regards,
Peter T

 

[Peter T]

It doesn't show up for me there either (and neither does much else), though
it does in my newsreader which is where I post from.

Regards,
Peter T

 

[Brian Murphy]

I haven't encountered any more trouble so far. In my various loops
that operate on groups of selected charts, I think for consistent
behavior it's important that the "selection" state of the last chart
be the same at the conclusion of the loop. I offer these two routines
that seem to do the job of getting the ActiveChart into either of the
two possible selection states regardless of what state it's in. I
don't claim them to be bullet proof, but they do help me handle the
"goofy state" that excel sometimes gets stuck in. So far they are
helping me. Maybe they will help someone else, too.

Brian


Sub GetIntoChartEditMode() 'created 10/17/2009
'want to get into edit mode, in xl2007 this is Halo mode
'in xl 2003 this should leave the chart with large square handles
'in xl 2007 this should leave the chart in a Halo
Dim s$
If TypeName(ActiveSheet) = "Chart" Then Exit Sub 'exit if on a
chartsheet
If TypeName(ActiveChart) = "Nothing" Then Exit Sub 'exactly one
chart must be active
s = TypeName(Selection)
If s <> "ChartArea" Then
If XL2007 Then
If s = "ChartObject" Then
If ActiveChart.SeriesCollection.Count > 0 Then
ActiveChart.SeriesCollection(1).Select 'to get halo to show
ActiveChart.Refresh
End If
ActiveChart.ChartArea.Select
Else
ActiveChart.Parent.Activate
If s <> "ChartObject" Then ActiveChart.ChartArea.Select
'in case something is selected inside the chart
End If
End If
End Sub

Sub GetOutOfChartEditMode() 'created 10/17/2009
'this should leave the chart "selected" with small selection handles
showing in either xl 2003 or 2007
If TypeName(ActiveSheet) = "Chart" Then Exit Sub 'exit if on a
chartsheet
If TypeName(ActiveChart) = "Nothing" Then Exit Sub 'exactly one
chart must be active
If XL2007 Then
If TypeName(Selection) = "ChartArea" Then
If ActiveChart.SeriesCollection.Count > 0 Then
ActiveChart.SeriesCollection(1).Select 'to get handles to show
End If
ActiveChart.Parent.Select
Else
If TypeName(Selection) <> "ChartObject" Then
ActiveWindow.Visible = False
End If
End Sub

 

[Peter T]

Thanks for those but not sure I've got them right, eg

If XL2007 Then
If TypeName(Selection) = "ChartArea" Then
else
If TypeName(Selection) <> "ChartObject" Then
ActiveWindow.Visible = False

ActiveWindow.Visible = False is for xl2003 and earlier, not xl2007


My idea of "goofy state" might be different to yours and I don't have a
problem switching between chartobject and a chart-item (if anything can be
more difficult in 2003). For me, I mean as relates to the selection of
multiple charts in 2007.

Hold Ctrl and select a single chart, gives "halo" handles denoting a
chartobject
Start again and select (say) a chart area, "full" selection handles appear.
With the chart selected, hold Ctrl and select another chart.
The two charts "might" now have "full" selection handles instead of each
with halos, yet even without the halos it's a selection of chartobjects.
Carry on selecting charts and intermittently a selected chart may or may not
have any handles at all. It may or may not be part of the selection, only
way to tell is to move or copy the charts.

When multiple charts are selected, Selection.Count returns the count of
*all* chartobjects, not the qty selected (if less than the total).

Once getting into this vague "goofy state", there doesn't seem to be any way
of starting afresh!

Regards,
Peter T

 

[Jon Peltier]

Peter -

Further... The selection handles should distinguish between selection
as a chart + item or just as chartobject.

You're not using 2007, are you? There are not longer selection handles
for a chart or a chart object. Instead, the whole chart acquires a thick
ugly border for an active chart or for a selected chart object (same
appearance for both). It is no longer possible to see by inspection what
is the selection mode.

- Jon

 

[Peter T]

Hi Jon,

You're not using 2007, are you? There are not longer selection handles for
a chart or a chart object. Instead, the whole chart acquires a thick ugly
border for an active chart or for a selected chart object (same appearance
for both). It is no longer possible to see by inspection what is the
selection mode.

But I DO get old style chartobject like handles when selecting a chartobject
with Ctrl in 2007, or as Brian terms them "halos". There's just a couple
slight differences
- only 4 halos on each corner 2007, 2003 has another 4 at the mid point of
each side
- the halos are lightly filled

I can alternate between the selection you describe and the halos by simply
re-clicking the chartarea and toggling Ctrl

Regards,
Peter T

 

[Brian Murphy]

Peter & Jon,

www.xlrotor.com/temp/Seclecting_multiple_charts.pdf

The above file documents what I've learned about the behavior of excel
2007. It is possible to get 4 little circle handles to show up on one
selected chart and this is closely connected to "goofy" mode.

I think newsgroup word wrap make it look like
Activewindow.Visible=false is used for XL2007. One long IF on a
single line got split into two. I'll paste it here again and try to
format it better.

If XL2007 Then
If TypeName(Selection) = "ChartArea" Then
If ActiveChart.SeriesCollection.Count > 0 Then _
ActiveChart.SeriesCollection(1).Select 'to get
handles to show
End If
ActiveChart.Parent.Select
Else ' if not xl2007
If TypeName(Selection) <> "ChartObject" Then
ActiveWindow.Visible = False
End If

Brian

 

[Brian Murphy]

Just to clarify. For Excel 2007 I've been using "halo" mode to refer
to a single halo surrounding an entire chart. I call "handle" mode
or "selected" mode where you see 4 small round circles at the corners.

Brian

 

[Brian Murphy]

Oops. There was a typing error in the URL. This one should work.

www.xlrotor.com/temp/Selecting_multiple_charts.pdf

Brian

 

[Peter T]

Thanks for putting that up, though my take is slightly different in one or
two areas.

I don't think there's anything wrong with that four cornered halo selection,
ie a single chartobject. You can get that, as you say, by holding Ctrl and
clicking on the chart. Or, in code by doing say
ActiveSheet.DrawingObjects(1).Select
? typename(selection) ' ChartObject

What seems odd to me is we don't get that apparent selection, the halos,
when multiple chartobjects are selected.

Start again, assuming the only shapes on the sheet are chartobjects do

ActiveSheet.DrawingObjects.Select '
' in earlier versions .ChartObjects.Select

This should select all chartobjects. But intermittently it might not or
might not, and if not thereafter no way to select multiple charts, manually
or programmatically without starting afresh (eg select cells).

Holding Shift doesn't seem to make any difference for me either way (as you
suggest in the pdf)

Jon - do you really never see the chartobject like selection handles
(halos), like the second chart in Brian's pdf.

Regards,
Peter T