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!

xidgel · Accepted Answer · 2015-07-30T15:24:10.563

5

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.

edited Jul 30 '15 at 15:24

answered Jul 28 '15 at 18:49

xidgel

3,085
2
13
22

Thanks! That added a button to each page! When I add the button properties can I just put my vba print code or is there a way to set the code so that it only activates when pressed?? – Cam Jul 28 '15 at 19:27
1

@Cam you should create a new Sub, and have that sub contain your code to print. The sub should require a variable called when a button is pressed (search for information on passing variables to subs). Then in each button have the code "Call MyPrintSub(BUTTON_VARIABLE)". And then you won't need to copy the code from each button individually, you just need to call the sub from each button. This will make it easier to edit for all buttons. – Grade 'Eh' Bacon Jul 28 '15 at 19:59
Sorry I am pretty new to VBA and coding and I think what you are saying makes sense but I don't understand how to implement. Do you have any examples of code for this? @Grade'Eh'Bacon – Cam Jul 28 '15 at 20:28
@Cam try looking at these resources; if you are having difficulties implementing the suggestions please ask a new question highlighting what you have tried and what failed: http://stackoverflow.com/a/6252348/5090027 http://stackoverflow.com/questions/2804327/call-a-subroutine-from-a-different-module-in-vba – Grade 'Eh' Bacon Jul 29 '15 at 14:52

score 1 · Answer 2 · answered Jul 29 '15 at 22:31

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:

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

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

How to add a command button in VBA?

2 Answers2

Linked