CODEWITHSUNDEEP
vbaexcel
cyb3rwolf
cyb3rwolf

Reputation: 13

Have event for another open workbook

I have a spreadsheet template that is sent out to customers. Since it is sent to a third party, the spreadsheet cannot have any macros in it. What I would like to accomplish is once the spreadsheet is sent back to me, have the ability to tie a macro I wrote to double clicking a cell in the returned spreadsheet (the macro is stored in a separate spreadsheet I have). Is it possible to have one spreadsheet watch events on another open spreadsheet?

Upvotes: 1

Views: 375

Answers (1)

Excel Developers
Excel Developers

Reputation: 2825

In a standard code module:

Public mySheetClass as new CustomSheet

Public Sub Init()
    Set mySheetClass.mySheet = NonMacroWorkbook.WorkSheets("TheSheet")
End Sub

In a class module, named CustomSheet:

Public WithEvents mySheet As Worksheet

Private Sub mySheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox "Foo"
End Sub

Then run Init.

Upvotes: 2

Related Questions