vba 为生成表单控件按钮的宏设置按钮位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10510941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting Button Location for Macro that Generates Form Control Buttons
提问by BiGXERO
I currently have data that I am splitting into multiple sheets and allowing the user to select which sheet they run the macro on. The work sheet is used as a filtration between multiple systems
我目前将数据拆分为多个工作表，并允许用户选择他们在哪个工作表上运行宏。工作表用作多个系统之间的过滤
I have a named range (dayList) that groups the data into days, and creates a new sheet for each day (day1, day2, etc). Each sheet must then have another macro applied (screen data) that will filter the data in each of these sheets in exactly the same way.
我有一个命名范围 (dayList)，它将数据分组为天，并为每一天（第 1 天、第 2 天等）创建一个新工作表。然后，每个工作表必须应用另一个宏（屏幕数据），该宏将以完全相同的方式过滤每个工作表中的数据。
I am trying to create a UI that will approximate the user being able to click on one of the cells in the named range to run the macro for that particular day. The code snippet im currently working with is below.
我正在尝试创建一个 UI，该 UI 将近似于用户能够单击命名范围中的一个单元格来运行该特定日期的宏。我目前正在使用的代码片段如下。
Sheets("LaunchScreen").Activate
Cells(rowCounter, 6).Value = "Day" & dayCounter
ActiveSheet.Buttons.Add(538.5, 56.25, 48.75, 13.5).Name = "Day" & dayCounter
ActiveSheet.Buttons("Day" & dayCounter).Select
Selection.OnAction = "JoinTransactionAndFMMS"
Im looping this to create a new button for each day then pass the button name to another macro as a parameter to find the worksheet in this workbook that shares the same name.
我循环这个来为每一天创建一个新按钮，然后将按钮名称作为参数传递给另一个宏，以在此工作簿中查找共享相同名称的工作表。
TLDR: I need to:
TLDR：我需要：
- Set the location of a button using VBA, preferably matching location to a cell reference e.g.
Range("A1").Button.Insert - Pass a cell reference from a named range into a macro
- 使用 VBA 设置按钮的位置，最好将位置与单元格引用匹配，例如
Range("A1").Button.Insert - 将命名区域中的单元格引用传递到宏中
An excel noob in way over his head here. Any help would be greatly appreciated!
一个优秀的菜鸟在这里。任何帮助将不胜感激！
采纳答案by Jerry Beaucaire
1) The .Add method expects 4 parameters. The first two are TOP and LEFT positions to get the UPPER LEFT corner of the button, the 3rd and 4th are height and width settings for the button.
1) .Add 方法需要 4 个参数。前两个是 TOP 和 LEFT 位置，用于获取按钮的左上角，第三个和第四个是按钮的高度和宽度设置。
So, if you know you want to add a button and the reference is to position it at cell C10 (a better example than A1), then this would do it:
因此，如果您知道要添加一个按钮并且引用是将其放置在单元格 C10（比 A1 更好的示例），则可以这样做：
ActiveSheet.Buttons.Add(Range("C10").Top, Range("C10").Left, 48.75, 13.5).Name = "Day" & dayCounter
2) Your macro has to first be designed to accept a parameter being "passed" into it. So, something like
2）您的宏必须首先设计为接受“传递”到其中的参数。所以，像
Sub MyMacro(MyRange As Range)
MsgBox MyRange.Address
End Sub
Now, your other macro can call MyMacroand you must pass in the parameter as it is called:
现在，您的另一个宏可以调用MyMacro并且您必须在调用时传入参数：
Sub test2()
Call MyMacro(Range("Animals"))
End Sub
