当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - How to add a command button in VBA?

问题描述:

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

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.

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!

网友答案:

Try this:

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 and Y determine the location, W and H determine the button size.

网友答案:

This will add a button (Form Control) and assign an existing macro to it.

Sub test()
    Dim cb As Shape
    Set cb = Sheet1.Shapes.AddFormControl(xlButtonControl, 10, 10, 100, 25)
    cb.OnAction = "PrintMacro"
End Sub

Private Sub PrintMacro()
    MsgBox "Test" ' for testing pursposes
    ' you actually put your print code here
End Sub

Now to add buttons from Sheet 5 onwards only, you can try:

  1. Producing a list of all your sheet names (if there's only a few of them)

    Dim shname
    For Each shname In Array("Sheet 5", "Sheet 6", "Sheet 7")
        test Sheets(shname) ' note that you'll have to use below test sub
    Next
    
  2. Do it the other way around. Make a list of what to exclude and test every sheet if it is on the list or not.

    Dim sh As Worksheet
    Dim xcludesheet: xcludesheet = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
    For Each sh In Worksheets
        If IsError(Application.Match(sh.Name, xcludesheet, 0)) Then
            test Sheets(sh.Name)
        End If
    Next
    

Your test sub to be used in above samples.

Sub test(ws As Worksheet)
    Dim cb As Shape
    Set cb = ws.Shapes.AddFormControl(xlButtonControl, 10, 10, 100, 25)
    cb.OnAction = "PrintMacro"
End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: