Call macro when a change is made to a Workbook

[mdo8105]

I have a workook that I will constantly be adding and removing worksheets. I have a macro that works based off the active sheet. I am trying to figure out how to call that macro at any point if there is a change made to the Workbook.

Thank you,

 

[Logit]

Try placing your macro, or a call to the macro, in ThisWorkbook module under either "SheetChange" or "SheetSelectionChange" events.

 

[mdo8105]

Thank you for replying. Here is the code that I have as of right now:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("B1")) Is Nothing Then
        On Error Resume Next
        Sh.Name = Sh.Range("B1").Value
    End If
Dim KeyCell As Range
If Not Application.Intersect(KeyCell, Range(Target.Address)) Is Nothing Then
Call UpdateForm
End If
End Sub

The first part of the code works fine, my concern is the second event that is happening. When I add a msg box after the Then statment works fine, but when I try to call the macro "UpdateForm" It gives me a compile error stating: Expected variable or procedure, not module

 

[Logit]

Is this a Userform or a Sub ?

"Call UpdateForm"

What line in the code is highlighted when you receive the error ?

 

[mdo8105]

I was able to fix that error by changing my macro name "UpdateForm" I think the logic couldn't distinguish that it was a module. The new issue I am running into is The code is calling my macro that sends an email multiple times. I have the Code attached to ThisWorkbook in my Current workbook: Code:

Private Sub Workbook_Open()    prevVal = Worksheets("Tracker").Range("F10").Value
    prevVal1 = Worksheets("Tracker").Range("F11").Value
End Sub


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If prevVal = "Passed" Then
[B][U]        Call Mail_workbook_Outlook_1[/U][/B]
        Application.EnableEvents = False
    End If
    prevVal = Worksheets("Tracker").Range("F10").Value
    Application.EnableEvents = True
    If prevVal1 = "Passed" Then
        Call Mail_workbook_Outlook_2
        Application.EnableEvents = False
    End If
    prevVal1 = Worksheets("Tracker").Range("F11").Value
    Application.EnableEvents = True


    End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("B1")) Is Nothing Then
        On Error Resume Next
        Sh.Name = Sh.Range("B1").Value
    End If
Dim KeyCell As Range
Set KeyCell = ActiveSheet.Range("I:I")
If Not Application.Intersect(KeyCell, Range(Target.Address)) Is Nothing Then
Call FUpDate
Call F3C
Call F3B
On Error GoTo bm_Safe_Exit
Application.EnableEvents = False
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub

The code that is underlined and bold is what is being called mulitple times.

Thank you again for helping me.

 

[Logit]

.
I believe you should move this macro

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If prevVal = "Passed" Then
        Call Mail_workbook_Outlook_1
        Application.EnableEvents = False
    End If
    prevVal = Worksheets("Tracker").Range("F10").Value
    Application.EnableEvents = True
    If prevVal1 = "Passed" Then
        Call Mail_workbook_Outlook_2
        Application.EnableEvents = False
    End If
    prevVal1 = Worksheets("Tracker").Range("F11").Value
    Application.EnableEvents = True

out of ThisWorkbook and place it in the Sheet module "Sheet Change" event. Having it in ThisWorkbook is telling Excel anytime any sheet in the workbook has a change of any kind,
it should send the email.

If you place it into the Sheet module, it will only fire when that specific sheet (Tracker) has the change.

I agree with you, carefully selecting your macro names is important. Using VBA commands as names for a macro does confuse Excel.


Here is one of my examples: " Sub shwFrm() End Sub"

Vba is ok with "shwFrm" but has caused me headaches if I use "Sub showForm ()"