With函数循环遍历所有工作表中的多对象表达

本文介绍了With函数循环遍历所有工作表中的多对象表达的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个由两个工作表( 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