VBA running script for when a specific cell in another workbook changes-CodePudding

Trying to create a script which detects when a cell in another workbook changes "B2" and once the change is detected it will run the macro RUNALL which has already been created and is working. Runall will run a number of different macros, which saves as a pdf and sends an email to the customer. Any help would be greatly appreciated

    Sub Worksheet_Changes(ByVal Target As Range)

' Run the code when cell B2 is changed
If Target.Address = Workbook("M:\Wholesale\Test.xlsx").Sheet("Sheet1").Range("B2").Address Then


Call RUNALL

End If
End Sub

CodePudding user response：

Read up on Application events: https://docs.microsoft.com/en-us/office/troubleshoot/excel/create-application-level-event-handler

In a class module clsAppEvents:

Option Explicit

Private WithEvents app As Excel.Application
Private cellToMonitor As Range

Private Sub app_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Parent.Name = cellToMonitor.Worksheet.Parent.Name Then
        If Sh.Name = cellToMonitor.Worksheet.Name Then
            If Not Application.Intersect(Target, cellToMonitor) Is Nothing Then
                Debug.Print "Changed " & cellToMonitor.Address & " on " & _
                             Sh.Name & " in " & Sh.Parent.Name
            End If
        End If
    End If
End Sub

Private Sub Class_Initialize()
    Set app = Application
End Sub

Property Set TheCell(c As Range)
    Set cellToMonitor = c
End Property

In a regular module:

Option Explicit

Private obj

Sub Tester()
    Set obj = New clsAppEvts
    Set obj.TheCell = Workbooks("Book2").Sheets(1).Range("A3") 'for example
End Sub

CodePudding user response：

As BigBen has pointed out, the Worksheet_Changes is triggered only when there is a change in the sheet where your script is specifically located, therefore you should move the macro to the sheet's code located on the other workbook (the one that suffers the change).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$2" Then
        Application.Run ("'M:\Wholesale\My_Book.xlsm'!RUNALL")
    End If
End Sub

Since the RUNALL macro will be located in a different workbook, you should use the Application.Run() method as findwindow also pointed out to make the reference to a different workbook. In this case "My_book.xlsm" is the one containing the RUNALL macro.

Moreover, note that Workbook("M:\Wholesale\Test.xlsx").Sheet("Sheet1").Range("B2").Address will only return $B$2 making no difference between the two workbooks.