如何在VBA中添加命令按钮？ [英] How to add a command button in VBA?

问题描述

我正在尝试向Excel工作簿中添加一个按钮，以便它显示在每个工作表中。一个很好的答案回答了我原来的问题，这给了我一个宏来在每张纸上创建按钮：

I am trying to add a button to an Excel workbook so that it shows up in every sheet. A great answer to my original question gave me a macro to create the buttons on each sheet:

Sub AddButtons()
    Dim ws As Excel.Worksheet
    Dim btn As Button

    For Each ws In ThisWorkbook.Worksheets
        Set btn = ws.Buttons.Add(X, Y, W, H)
        [set btn properties]
    Next ws
End Sub

我现在在设置按钮属性时遇到麻烦，因此按下按钮时该按钮将打印纸张。再次是我的打印宏：

I am now having trouble with setting the button properties so that the button prints the sheet when pressed. Again here is my print macro:

 Dim WS_Count As Integer
 Dim i As Integer

 ' Set WS_Count equal to the number of worksheets in the active workbook.
 WS_Count = ActiveWorkbook.Worksheets.Count
 'allows user to set printer they want to use
 Application.Dialogs(xlDialogPrinterSetup).Show
 ' Begin the loop.
 For i = 5 To WS_Count
   Worksheets(i).Activate
   With ActiveWorkbook.Worksheets(i).PageSetup
     .PrintArea = "A1:O48"
     .Orientation = xlLandscape
     .Zoom = False
     .FitToPagesTall = 1
     .FitToPagesWide = 1
   End With
   ActiveWorkbook.Worksheets(i).PrintOut

关于如何将此宏合并到按钮属性中（传递变量并创建新的打印件）有一些好的建议子），但我对VBA还是很陌生，但未能成功使用它。理想情况下，我将拥有一个用于创建按钮的按钮宏，并且每次按下按钮时都会为每张纸调用打印宏。

There have been some good suggestions about how to go about incorporating this macro into the button properties (passing variables and creating a new print sub) however I am pretty new to VBA and have been unsuccessful in getting this to work. Ideally I would have a button macro that creates the button and every time it is pressed calls the print macro for each sheet.

最后一件事，我试图更改按钮代码，以便仅将按钮添加到工作表5上。如果有人也知道如何做到这一点，那将是很好的？

One last thing, I am trying to change the button code so that it only adds buttons to sheet 5 onwards. It would be great if anyone knew how to do that as well?

任何建议都是有帮助的，非常感谢！

Any advice is helpful and greatly appreciated!

推荐答案

尝试一下：

Sub AddButtons()
    Dim ws As Excel.Worksheet
    Dim btn As Button

    For Each ws In ThisWorkbook.Worksheets
        Set btn = ws.Buttons.Add(X, Y, W, H)
        btn.OnAction = "MySub"    ' MySub is executed when btn is clicked
        ' Substitute the name of your printing subroutine
        btn.Caption = "Print"
        'set additional btn properties as needed
    Next ws
End Sub

X 和 Y 确定位置， W 和 H 确定按钮的大小。

X and Y determine the location, W and H determine the button size.

这篇关于如何在VBA中添加命令按钮？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！