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:
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]
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
' Begin the loop.
For i = 5 To WS_Count
.PrintArea = "A1:O48"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
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!
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
Y determine the location,
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:
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