How to add a command button in VBA?

Question

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!

Answer 1

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.

Answer 2

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