Applying Button+Macro across all worksheets

Question

Forgive my ignorance (newby and little knowledge of VBA)...

I have developed some macros that are attached to buttons, and working in one worksheet in a workbook. The macros perform various jobs on a calendar. There is one calendar for each of 10 bedrooms in the wing of a hospital.

I now want to make identical worksheets with the same buttons and macros for each bedroom i.e. 10 worksheets.

But try as I might I cant get the macros to work in the other worksheets.

The macros are in the VBA code editor for the first worksheet (Bed1). I have copied the code into the "This Workbook" page within the VBA editor - but that had no effect, other than to stop them working at all.

This is a typical macro:

'============================================
Private Sub Prevw1_Click()
'============================================
' DAILY PATIENT TIMETABLE
' PRINT PREVIEW
'============================================
    ActiveSheet.Select
    ActiveSheet.AutoFilterMode = False
    Range("_Daily").Select
    ActiveSheet.PageSetup.PrintArea = "_Daily"
'
    Call page_SetUp
'
' Variations for page setup
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(1.5)
        .RightMargin = Application.InchesToPoints(0.9)
        .Zoom = 75
    End With
    ActiveSheet.PrintPreview
    ActiveSheet.PageSetup.PrintArea = ""
    Range("H126, H126").Select
End Sub

Q. What have I done wrong that makes this only work in the Bed1 worksheet where it was developed first?

Kind regards Russ

David Zemens David Zemens · Accepted Answer · 2013-10-23T19:42:43

Take the code out of the ThisWorkbook module and put it in a normal code module. In Design Mode, in the Excel window (not VBE), right-click the button and do Assign Macro, then choose the macro "Prevw1_Click". That should work. You'll have to assign the macro to each button, or you could simply copy/paste the button to the other sheets.

If your button is an ActiveX Control, then I think you may need to have the subroutine for each button in the worksheet where the button resides. So, each worksheet may have an activeX command button called "CommandButton1", then each Worksheet code module should have a subroutine like:

Sub CommandButton1_Click()
    Call ClickTheButton
End Sub

You will basically put all of this same code in each of the 10 worksheet code modules. Then, rename your routine in the ordinary code module, like:

Private Sub ClickTheButton()
'============================================
' DAILY PATIENT TIMETABLE
' PRINT PREVIEW
'============================================
    ActiveSheet.Select
    ActiveSheet.AutoFilterMode = False
    Range("_Daily").Select
    ActiveSheet.PageSetup.PrintArea = "_Daily"
'
    Call page_SetUp
'
' Variations for page setup
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(1.5)
        .RightMargin = Application.InchesToPoints(0.9)
        .Zoom = 75
    End With
    ActiveSheet.PrintPreview
    ActiveSheet.PageSetup.PrintArea = ""
    Range("H126, H126").Select
End Sub

The reason I would do this, instead of copying the existing macro to each of 10 worksheets is simple: If you ever need to modify your subroutine, you only need to modify it in one place. Likewise, if you add a new worksheet(s) you need only copy 3 lines of code instead of 20. It's just easier to maintain this way, since each sheet's button is calling the same code, each sheet's button should just have a simple sub that calls the "main" procedure.

Applying Button+Macro across all worksheets

1 Answers