WorkSheet_Change VBA that uses another stored Macro

[Bowlnhokie]

I have 20 sheets in an excel workbook that I want to leverage Worksheet_Change events to make updates. Is there a way I can store my Macro in a different module that the On Change event can then call. I would like to do this so If I need to make a change to the Logic, I only have to change it in one module instead of every sheet.

When I run the below code, it errors on "If Target.Address = Target.EntireRow.Address Then Exit Sub" with Run-time error '424' Object required. I am assuming I am not defining something correctly but I am not sure what I need to do.

Any help that can be provided would be appreciated.

Below is my code (probably a little rough)

Private Sub Worksheet_Change(ByVal Target As Range)

Run "OnEventChange"
    
End Sub

Sub OnEventChange()

    Dim Cell As Range
    Dim dblEnteredByCol, dblLastEditedCol, dblRangeCount As Double
    Dim dlba, dblb As Double
        
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
               
        dbla = 0
        Do Until Range("A8").Offset(0, dbla).Value = "Entered_By"
            dbla = dbla + 1
            Loop
        dblEnteredByCol = Range("A8").Offset(0, dbla).Column
        
        dbla = 0
        Do Until Range("A8").Offset(0, dbla).Value = "Last_Edited"
            dbla = dbla + 1
            Loop
        dblLastEditedCol = Range("A8").Offset(0, dbla).Column

    If Target.Address = Target.EntireRow.Address Then Exit Sub
    
    dblTargetRangeCount = Range(Target.Address).Count
    
    For Each Cell In Target
        If Cell.Column >= dblPeriodCol And Cell.Column < dblEnteredByCol And Cell.Row > dblPeriodRow Then
        'ActiveSheet.Unprotect Password:=SHEETPASS
            If Cell.Value <> "" Then
                  Cell.Offset(0, dblEnteredByCol - Cell.Column) = Application.UserName
                  Cell.Offset(0, dblLastEditedCol - Cell.Column) = Now
            Else
                  Cell.Offset(0, dblEnteredByCol - Cell.Column).ClearContents
                  Cell.Offset(0, dblLastEditedCol - Cell.Column).ClearContents
            End If

        Else
        End If
    Next Cell
    
        'ActiveSheet.Protect Password:=SHEETPASS, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        ', AllowFiltering:=True
    
    dbla = 0
    dblb = 0
    
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

 

[JamesW]

Hi,

You need to pass Target to the other subroutine.

Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call OnEventChange(Target)
End Sub
Sub OnEventChange(trgt As Range)
    MsgBox trgt.Value
End Sub

 

[Bowlnhokie]

Rookied mistake by me, thank you very much.