CODEWITHSUNDEEP
vbaexcelevents
vacip
vacip

Reputation: 5426

Catch an event in a different workbook

I was wondering if it was possible to catch an event in another workbook.

A macro will open a workbook for the user to use, and I want to be able to run another macro on its selection_change event. No, the code can't be stored in the just opened workbook.

Is there any other way than programmatically adding a code to the opened workbook's ThisWorkbook module?

That way is unsafe and unstable and generally bleh.

Upvotes: 4

Views: 3009

Answers (1)

Comintern
Comintern

Reputation: 22205

All you need to do it grab a WithEvents reference to the opened workbook in a class module with your event handlers. For example:

'In ThisWorkbook 
Option Explicit

Private WithEvents other As Worksheet

Private Sub Example()

    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Dev\other.xlsx")
    Set other = wb.Sheets("Sheet1")

End Sub

Private Sub other_SelectionChange(ByVal Target As Range)

    Debug.Print Target.Address

End Sub

Upvotes: 3

Related Questions