With函数循环遍历所有工作表中的多对象表达 [英] Multiple object expression in With function looping through all sheets

问题描述

我有一个由两个工作表( Sheet1 和 Sheet2 )组成的Excel电子表格.在每张纸上，我都有一个 Button 1 .
为了在两个工作表中将此按钮移动到 Range("D9:E11")，我使用以下VBA引用解决方案

I have an Excel spreadsheet consisting of two sheets (Sheet1 and Sheet2). In each sheet I have a Button 1.
In order to move this button to Range("D9:E11") in both sheets I use the following VBA refering to the solution here:

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

到目前为止，所有这些都可以正常运行.

All this works perfectly so far.

但是，现在此Excel文件将变大，而不是两张纸，我将得到更多的纸页(例如30张纸).在这种情况下，我必须将所有这些表添加到 Sub Sample().

However, now this Excel file will get bigger and instead of two sheets I will have much more sheets (for example 30 Sheets). In this case I would have to add all of those sheets to the Sub Sample ().

我需要在上面的代码中进行哪些更改，以使其独立于工作表的数量，因此无论有多少工作表，按钮都移至所有工作表的 Range("D9:D11")我有床单吗?

What do I need to change in the code above to make it independent from the number of sheets so the button is moved to Range("D9:D11") in all sheets no matter how many sheets I have?

推荐答案

这很简单.遵循相同的逻辑，并声明一个 Optional 布尔变量，例如 AllSheets .

It is pretty simple. Follow the same logic and declare a Optional boolean variable say, AllSheets.

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

Sub MoveButton(sh As Worksheet, btnName As String, Optional AllSheets As Boolean)
    Dim Range_Position As Range
    Dim ws As Worksheet

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

    If AllSheets = True Then
        For Each ws In ThisWorkbook.Sheets
            With ws.Buttons(btnName)
                .Top = Range_Position.Top
                .Left = Range_Position.Left
                .Width = Range_Position.Width
                .Height = Range_Position.Height
                .Text = "Button"
            End With
        Next ws
    Else
        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 If
End Sub

这篇关于With函数循环遍历所有工作表中的多对象表达的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！