使用 VBA 操作 excel“自动形状”

Manipulating excel "autoshapes" with VBA

提问by AME

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".

我正在尝试在 VBA (Excel) 中编写一个分配给复选框的宏。每当单击复选框时，“自动形状”会将其“顺序”从“发送到后面”更改为“发送到前面”。

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,

谢谢，

回答by Nick Spreitzer

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.

您应该从 MsoZOrderCmd 枚举中选择所需的 ZOrder 移动，并且您的代码应该位于复选框控件的 Change 事件例程中。

---

---

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:

此外，要获取形状的名称，请单击它并查看 Excel 左上角（工具栏下方）的名称框，或者像这样遍历所有形状：

Sub Macro1()

Dim MyShape As Shape

For Each MyShape In Sheet1.Shapes

    Debug.Print MyShape.Name

Next MyShape

End Sub