带功能VBA中的多对象表达 [英] Multiple object expression in With function VBA

问题描述

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

I have a button in my Excel spreadsheet and I move the position of this button using the following 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

到目前为止，所有这些都工作正常.

All this works fine so far.

但是，我在Excel文件的Sheet2上也有一个Button 1，我想用与Sheet2上的按钮相同的方式移动此按钮.解决此问题的一种方法是制作第二个Sub，其中将Sheet1更改为Sheet2.
但是，我想知道是否有更有效的解决方案，可以在VBA的With函数中将两个工作表合并在一起.
类似的东西:

However, I also have a Button 1 on Sheet2 of the Excel file and I want to move this button the same way I do it for the button on Sheet2. One way to solve this issue is to make a second Sub in which I change Sheet1 to Sheet2.
However, I am wondering if there is more efficient solution in which I combine both sheets in the With function in the VBA.
Something like this:

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

推荐答案

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

Paste this code in a Module and simply pass the button name to the Sub Sample(). Of course you may want to do do error handling to check if the right button name is sent to 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将记录必要的工作表.

The Set ws = ActiveSheet will take of the necessary sheet.

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

However, if you want to specify the sheet name as well then use this

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

编辑

非常感谢您的回答.它几乎可以满足我的要求.问题是，无论哪个页面处于活动状态，都应在两个页面(Sheet1和Sheet2)中移动按钮1.因此，如果我当前在Sheet1上并且在Sheet1和Sheet2中都运行了VBA，则应将其移至范围D9:D11

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

当您不想在两个页面中都移动按钮时，只能使用

When you do not want to move a button in both sheets then use only

MoveButton Sheet2, "Button 1"

请勿指定可选的第3个参数.

Do not specify the 3rd parameter which is optional.

这篇关于带功能VBA中的多对象表达的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！