Manipulating excel "autoshapes" with VBA

Question

I am trying to write a macro in VBA (Excel) that is assigned to a Checkbox. Whenever the checkbox is clicked, an "autoshape" will change its "order" from "Send to Back" to "Send to Front".

Basically, I am trying to create a dashboard with multiple panels, so that users can access information without moving between sheets. Each panel will have a rectangular autoshape as its background and the components of the panel will be "grouped" within the autoshape.

Can this be done? I would greatly appreciate any ideas into writing the code.

Thanks,

Answer 1

I'm not quite following your larger goal, but in order to bring a shape to the front use this:

If MyCheckBox.Value = True Then
    MySheetName.Shapes("MyShapeName").ZOrder msoBringToFront
End If

You should select your desired ZOrder movement from the MsoZOrderCmd enumeration and your code should be in the Change event routine for your checkbox control.

---

EDIT:

You could also refer to the shape by its index number. For example:

MySheetName.Shapes(0).ZOrder msoBringToFront

Also, to get the name of a shape, either click it and look in the Name Box in the upper left corner of Excel (below the toolbars), or iterate through all the shapes like so:

Sub Macro1()

Dim MyShape As Shape

For Each MyShape In Sheet1.Shapes

    Debug.Print MyShape.Name

Next MyShape

End Sub