How do I combine separate macros for the same event in the 'ThisWorkbook' module?

Question

I have a macro in the 'ThisWorkbook' module set to run 'BeforeSave'. I have two other macros that I also need to run 'BeforeSave'. Can I add additional macros to this module?

I have created my macros in the 'standard' module section, and they work with the selection of the 'Run' button. I have attempted to add the 'Macro/Module names' to the bottom of my 'BeforeSave' macro which has done nothing but give me errors.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Call HideRows
  Call DivAdminApproval
  Call ProjNumbrReq
End Sub

Public Sub HideRows()

'When a row begins with X in Travel Expense Codes worksheet, hide the row

  Const beginRow As Long = 3
  Const endRow As Long = 38
  Const chkCol As Long = 14

  Dim rowCnt As Long
  Dim ws As Worksheet

  Set ws = ThisWorkbook.Worksheets("Travel Expense Codes")

  For rowCnt = endRow To beginRow Step -1
       With ws.Cells(rowCnt, chkCol)
           .EntireRow.Hidden = (.Value = "X")
       End With
  Next rowCnt
  End Sub

  Public Sub ProjNumbrReq()
  'Call ProjNumbrReq
   With Worksheets("Travel Expense Voucher")
        For Each myCell In .Range("U15:U45")
            If myCell.Value > 0 And .Cells(myCell.row, "N") = "" Then
               MsgBox "Project Number must be provided on each line where reimbursement is being claimed.", vbCritical, "Important:"
               Cancel = True
               Exit Sub
            End If
        Next myCell
    End Sub

  Public Sub DivAdminApproval()
  'Call DivAdminApproval
   With Worksheets("Travel Expense Voucher")
        If Worksheets("Travel Expense Voucher").Cells("F5") = 2 Then
        For Each myCell In .Range("O15:O45")
            If myCell.Value = 0.58 Then
                MsgBox "You have selected reimbursement at the 'HIGH' mileage rate ($.58/mile).  To receive reimbursement at this rate, Division Administrator Approval is Required.", vbCritical, "Important:"
                Exit Sub
            End If
        Next myCell

End Sub

The ProjNumbrReq and DivAdminApproval macros have been listed before End Sub, hoping that would call them to work. However, they are not running.

Have a before save macro that calls each of the 3 specific macros in turn. — Solar Mike

SNicolaou SNicolaou · Accepted Answer · 2019-11-10T06:16:22

something along these lines, if your macros are not in the 'ThisWorkbook' module then make sure they are defined public (instead of private):

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Call sMacro1
  Call sMacro2
  Call sMacro3
End Sub

Private Sub sMacro1()
  'do something
End Sub

Private Sub sMacro2()
  'do something
End Sub

Private Sub sMacro3()
  'do something
End Sub

How do I combine separate macros for the same event in the 'ThisWorkbook' module?

1 Answers