Can't select a range after moving a shape object

[Dennis Kuhn]

Caveat: I know this code isn't very elegant, but I'm under a deadline.

I've got three stacked objects on a report: two logos, and a white
rectangle. I'm running a macro that pushes the white rectangle to the
back, then pushes one of the logos to the back, effectively making
only one of them visible. The macro is basically a toggle for the
user to select which logo they want visible.

The macro code goes like this:

Sheets("Run Avg Sheet").Select
ActiveSheet.Shapes("Rectangle 54").Select
Selection.ShapeRange.ZOrder msoSendToBack
If Comp = "Main" Then
ActiveSheet.Shapes("Picture 55").Select
Selection.ShapeRange.ZOrder msoSendToBack
Else
ActiveSheet.Shapes("Picture 53").Select
Selection.ShapeRange.ZOrder msoSendToBack
End If
' ********WHY DOESN'T THIS WORK?????
Range("a1").Select

I get a "runtime error 1004: select method of range class failed"
error.

I know if I had more time I could screw around with this so I'm not
selecting and then sendingtoback, but again... deadline. I've tried

activesheet.shapes("Picture 53").selection.shaperange.zorder
msosendtoback

and

activesheet.shapes("Picture 53").shaperange.zorder msosendtoback

and other variants and haven't stumbled across the exact structure
necessary.

Can someone either give me the shortcut non-selecting version of these
three moves or give me a suggestion on another command that I need to
run to get the range select to work?

I don't want a graphic selected when I finish the macro if I can help
it.

Thanks in advance!

Dennis

 

[Dave Peterson]

I'm betting your code is behind a worksheet.

If that's true, then unqualified ranges (like Range("a1") refer to the sheet
that owns the code--not the sheet you have selected ("run avg sheet").

Maybe:

with Sheets("Run Avg Sheet")
.Select
.Shapes("Rectangle 54").Select
Selection.ShapeRange.ZOrder msoSendToBack
If Comp = "Main" Then
.Shapes("Picture 55").Select
Selection.ShapeRange.ZOrder msoSendToBack
Else
.Shapes("Picture 53").Select
Selection.ShapeRange.ZOrder msoSendToBack
End If
' ********WHY DOESN'T THIS WORK?????
.Range("a1").Select
end with

the dot's in front of those objects (shapes/ranges) means that it belongs to the
previous With object.

And instead of sending to back, you may want to:

with Sheets("Run Avg Sheet")
.Select
' kill the rectangle
' .Shapes("Rectangle 54").visible = false
If Comp = "Main" Then
.Shapes("Picture 55").visible = false
.shapes("picture 53").visible = true
Else
.Shapes("Picture 53").visible = false
.shapes("picture 55").visible = true
End If
' ********WHY DOESN'T THIS WORK?????
.Range("a1").Select
end with

(I may have mixed up the visibility, though.)

 

[Dennis Kuhn]

I'm betting your code is behind a worksheet.

If that's true, then unqualified ranges (like Range("a1") refer to the sheet
that owns the code--not the sheet you have selected ("run avg sheet").

And instead of sending to back, you may want to:

with Sheets("Run Avg Sheet")
.Select
' kill the rectangle
' .Shapes("Rectangle 54").visible = false
If Comp = "Main" Then
.Shapes("Picture 55").visible = false
.shapes("picture 53").visible = true
Else
.Shapes("Picture 53").visible = false
.shapes("picture 55").visible = true
End If
' ********WHY DOESN'T THIS WORK?????
.Range("a1").Select
end with

Ack! with SHEETS, not with SHAPES...

That was my mental block. This is actually what I was trying to do in
the first place, as you correctly guessed. Sorry I wasn't clear where
the code was originating from.

I was doing the sendtoback because I was struggling so hard with the
visible toggle. (I normally work in Access, which is just different
enough to cause me headaches.)

I'll be stealing your above code and applying it to other sections as
well.

Thanks very much for the assistance!

Dennis