excel - With 函数 VBA 中的多对象表达式

Question

我的 Excel 电子表格中有一个按钮，我使用以下 VBA 移动该按钮的位置:

Sub Positioning_Button()
Set Range_Position = Sheet1.Range("D9:E11")
With Sheet1.Buttons("Button 1")
.Top = Range_Position.Top
.Left = Range_Position.Left
.Width = Range_Position.Width
.Height = Range_Position.Height
.Text = "Button"
End With
End Sub

到目前为止，所有这些都运行良好。

<小时/>

但是，我还有一个Button 1上Sheet2 Excel 文件的名称，我想以与 Sheet2 上的按钮相同的方式移动此按钮。解决此问题的一种方法是制作第二个 Sub我在其中更改 Sheet1至Sheet2 。
但是，我想知道是否有更有效的解决方案，可以将 With 中的两张表结合起来。 VBA 中的函数。
像这样:

Sub Positioning_Button1()
Set Range_Position = Range("D9:E11")
With Sheet1.Buttons("Button 1") And Sheet2.Buttons("Button 1")
.Top = Range_Position.Top
.Left = Range_Position.Left
.Width = Range_Position.Width
.Height = Range_Position.Height
.Text = "Button"
End With
End Sub

Answer 1

将此代码粘贴到模块中，然后将按钮名称简单地传递给Sub Sample()。当然，您可能需要进行错误处理来检查正确的按钮名称是否发送到 MoveButton。

Sub Sample()
    MoveButton "Button 1"
End Sub

Sub MoveButton(btnName As String)
    Dim ws As Worksheet
    Dim Range_Position As Range

    Set ws = ActiveSheet
    Set Range_Position = ws.Range("D9:E11")

    With ws.Buttons(btnName)
        .Top = Range_Position.Top
        .Left = Range_Position.Left
        .Width = Range_Position.Width
        .Height = Range_Position.Height
        .Text = "Button"
    End With
End Sub

Set ws = ActiveSheet 将获取必要的工作表。

但是，如果您还想指定工作表名称，则使用此

Sub Sample()
    MoveButton Sheet1, "Button 1"
End Sub

Sub MoveButton(sh As Worksheet, btnName As String)
    Dim Range_Position As Range

    Set Range_Position = sh.Range("D9:E11")

    With sh.Buttons(btnName)
        .Top = Range_Position.Top
        .Left = Range_Position.Left
        .Width = Range_Position.Width
        .Height = Range_Position.Height
        .Text = "Button"
    End With
End Sub

编辑

Thanks a lot for your answer. It is almost doing what I want. The issue is that the Button 1 should be moved in both sheets (Sheet1 and Sheet2) no matter which one of those sheets is active. So if I am currently on Sheet1 and I run the VBA both in Sheet1 and Sheet2 it should be moved to Range D9:D11

你的意思是这样吗？

Sub Sample()
    MoveButton Sheet2, "Button 1", Sheet1
End Sub

Sub MoveButton(sh As Worksheet, btnName As String, Optional shB As Worksheet)
    Dim Range_Position As Range

    Set Range_Position = sh.Range("D9:E11")

    With sh.Buttons(btnName)
        .Top = Range_Position.Top
        .Left = Range_Position.Left
        .Width = Range_Position.Width
        .Height = Range_Position.Height
        .Text = "Button"
    End With

    If Not shB Is Nothing Then
        With shB.Buttons(btnName)
            .Top = Range_Position.Top
            .Left = Range_Position.Left
            .Width = Range_Position.Width
            .Height = Range_Position.Height
            .Text = "Button"
        End With
    End If
End Sub

当您不想在两个工作表中移动按钮时，仅使用

MoveButton Sheet2, "Button 1"

不要指定第三个参数，该参数是可选的。