vba 在所有工作表中应用按钮+宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19550693/
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
Applying Button+Macro across all worksheets
提问by user2912848
Forgive my ignorance (newby and little knowledge of VBA)...
原谅我的无知（新手，对 VBA 知之甚少）...
I have developed some macros that are attached to buttons, and working in one worksheet in a workbook. The macros perform various jobs on a calendar. There is one calendar for each of 10 bedrooms in the wing of a hospital.
我开发了一些附加到按钮的宏，并在工作簿中的一个工作表中工作。宏在日历上执行各种作业。医院翼楼的 10 间卧室各有一个日历。
I now want to make identical worksheets with the same buttons and macros for each bedroom i.e. 10 worksheets.
我现在想为每间卧室制作具有相同按钮和宏的相同工作表，即 10 个工作表。
But try as I might I cant get the macros to work in the other worksheets.
但是尽我所能，我无法让宏在其他工作表中工作。
The macros are in the VBA code editor for the first worksheet (Bed1). I have copied the code into the "This Workbook" page within the VBA editor - but that had no effect, other than to stop them working at all.
宏位于第一个工作表 (Bed1) 的 VBA 代码编辑器中。我已将代码复制到 VBA 编辑器中的“本工作簿”页面中 - 但这没有任何效果，除了完全停止它们工作之外。
This is a typical macro:
这是一个典型的宏：
'============================================
Private Sub Prevw1_Click()
'============================================
' DAILY PATIENT TIMETABLE
' PRINT PREVIEW
'============================================
ActiveSheet.Select
ActiveSheet.AutoFilterMode = False
Range("_Daily").Select
ActiveSheet.PageSetup.PrintArea = "_Daily"
'
Call page_SetUp
'
' Variations for page setup
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(1.5)
.RightMargin = Application.InchesToPoints(0.9)
.Zoom = 75
End With
ActiveSheet.PrintPreview
ActiveSheet.PageSetup.PrintArea = ""
Range("H126, H126").Select
End Sub
Q. What have I done wrong that makes this only work in the Bed1 worksheet where it was developed first?
问：我做错了什么，使它只能在最初开发的 Bed1 工作表中起作用？
Kind regards Russ
亲切的问候罗斯
采纳答案by David Zemens
Take the code out of the ThisWorkbookmodule and put it in a normal code module. In Design Mode, in the Excel window (not VBE), right-click the button and do Assign Macro, then choose the macro "Prevw1_Click". That should work. You'll have to assign the macro to each button, or you could simply copy/paste the button to the other sheets.
将代码从ThisWorkbook模块中取出，放入普通的代码模块中。在设计模式下，在 Excel 窗口（不是 VBE）中，右键单击按钮并执行Assign Macro，然后选择宏“Prevw1_Click”。那应该工作。您必须将宏分配给每个按钮，或者您可以简单地将按钮复制/粘贴到其他工作表。
If your button is an ActiveX Control, then I think you may need to have the subroutine for each button in the worksheet where the button resides. So, each worksheet may have an activeX command button called "CommandButton1", then each Worksheetcode module should have a subroutine like:
如果您的按钮是 ActiveX 控件，那么我认为您可能需要为按钮所在的工作表中的每个按钮设置子例程。因此，每个工作表可能有一个名为“CommandButton1”的 activeX 命令按钮，那么每个Worksheet代码模块都应该有一个子程序，如：
Sub CommandButton1_Click()
Call ClickTheButton
End Sub
You will basically put all of this same code in each of the 10 worksheet code modules. Then, rename your routine in the ordinary code module, like:
您基本上会将所有这些相同的代码放在 10 个工作表代码模块中的每一个中。然后，在普通代码模块中重命名您的例程，例如：
Private Sub ClickTheButton()
'============================================
' DAILY PATIENT TIMETABLE
' PRINT PREVIEW
'============================================
ActiveSheet.Select
ActiveSheet.AutoFilterMode = False
Range("_Daily").Select
ActiveSheet.PageSetup.PrintArea = "_Daily"
'
Call page_SetUp
'
' Variations for page setup
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(1.5)
.RightMargin = Application.InchesToPoints(0.9)
.Zoom = 75
End With
ActiveSheet.PrintPreview
ActiveSheet.PageSetup.PrintArea = ""
Range("H126, H126").Select
End Sub
The reason I would do this, instead of copying the existing macro to each of 10 worksheets is simple: If you ever need to modify your subroutine, you only need to modify it in one place. Likewise, if you add a new worksheet(s) you need only copy 3 lines of code instead of 20. It's just easier to maintain this way, since each sheet's button is calling the same code, each sheet's button should just have a simple sub that calls the "main" procedure.
我会这样做，而不是将现有宏复制到 10 个工作表中的每一个的原因很简单：如果您需要修改子例程，只需在一个地方修改它。同样，如果你添加一个新的工作表，你只需要复制 3 行代码而不是 20 行。这样维护起来更容易，因为每个工作表的按钮都调用相同的代码，每个工作表的按钮应该只有一个简单的子调用“主要”过程。
