Trigger Event in Wkbk A Based on Selection Event in Wkbk B - no Macros in Wkbk B

[JonXL]

Good afternoon all!

What I am trying to achieve is this:

- User opens Workbook A
- User clicks a button to open Workbook B so they can edit it (B is password protected, it can only be opened in write mode from book A; this is supposed to 'force' users to only use book A (and its userforms) to make changes to B)
- Workbook A has codes for userforms in it that show up when the user clicks on cells in Workbook B
- The userforms in book A effect changes in book B
- Book A has macros; book B does not

Now; I have the process all set up in book B using selection events on the worksheets, userforms, etc. It's working splendidly; however, as stated, I'd like for all this code and userforms to be stored in Workbook A and for workbook B to not be saved with any macros, code, etc.

So I guess what I'm looking for is whether there is a way to have the worksheet selection codes in book A somehow refer to the worksheets in book B so it is triggered when cells on the sheets in B are selected.

Is there a way to achieve this? I haven't seen anything on Google that covers similar topics (the closest I could get is applying worksheet changes in an umbrella manner over an entire workbook, but not between/across workbooks).

Maybe there is a different event trigger I could trap that then round-about checks whether the user has selected a target cell in book B?

Short of options here except the hardest workaround (temporarily loading book B's sheets into book A and writing them back in after the changes are made...)

Thank you to anyone for any help!

 

[JonXL]

So I found a couple of references to what I am trying to achieve. But like the posters who started those discussions, I am unable to get the suggested solutions to work.

The first was from here:

https://stackoverflow.com/questions/30832271/catch-an-event-in-a-different-workbook

I have this code in a class module called "MyClass"

Option Explicit

Private WithEvents other As Worksheet

Private Sub Example()

    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Users\Owner\Documents\BookTestOtherSheet.xlsx")
    Set other = wb.Sheets("Sheet1")

End Sub

Private Sub other_SelectionChange(ByVal Target As Range)

    Debug.Print Target.Address

End Sub

I can't figure out how to trigger this, though...

Then there's this:

https://stackoverflow.com/questions...-detect-another-worksheet-change-event-in-vba

Which I couldn't even figure out how to modify to work with another workbook... or what I would really even do to test that it works in a fresh workbook.

Is there something I can use to build on those codes to achieve what I'm looking for?

 

[mikerickson]

You could use Application Level events..

Put this is a class module in Workbook A

' in workbook A class module Class1

Public WithEvents myApp As Application

Private Sub myApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "WorkbookB" Then
        ' some code
    End If
End Sub

And instansize that class in the Open event of Workbook A

' in Workbook A ThisWorkbook code module

Dim myApplication As Class1

Private Sub Workbook_Open()
    Set myApplication = New Class1
    Set myApplication.myApp = ThisWorkbook.Parent
End Sub

 

[JonXL]

Thank you! That's the closest I've gotten so far. There's just one issue I'm still having...

When I set this up, I can get the code (which I just made to be a MsgBox popup for testing) to work when there is a sheet called "WorkbookB" in the same book as the workbook with the code.

However, I'm looking to get the event to trigger when a cell is selected on a sheet in another workbook. If it makes a huge difference in the code, it also needs to happen based on the sheet in the other workbook that is selected (I will have three userforms when this is done and each form comes up when the user is editing a different sheet).

I tried adding Wb As Object into the myApp_SheetSelectionChange procedure arguments and specifying its name, but I got an error when I loaded the workbook...

 

[JonXL]

I think I'm getting there... I modified your code to open a test book with the main book:

' in Workbook A ThisWorkbook code module

Dim myApplication As Class1

Private Sub Workbook_Open()
Workbooks.Open ("C:\Users\Owner\Documents\TestBookB.xlsx")
    Set myApplication = New Class1
    Set myApplication.myApp = ThisWorkbook.Parent
End Sub

Then I added the specific workbook reference to the Intersect test:

' in workbook A class module Class1

Public WithEvents myApp As Application

Private Sub myApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        If Not Intersect(Target, Workbooks("TestBookB.xlsx").Sheets("WorkbookB").Range("D4")) Is Nothing Then
            MsgBox "You clicked me"
        End If
End Sub

Now... when I click the cell D4 in the TestBookB.xlsx on WorkbookB worksheet, I get my MsgBox.

However, if I click any other cell (including ones in other workbooks and sheets), I get runtime errors; sometimes it's a subscript out of range, I also got a "method Intersect of object _Global failed"...

 

[JonXL]

With a little more trial and error, this code seems to do exactly what I need... I just had to change how I was testing for the right sheet/book:

' in workbook A class module Class1

Public WithEvents myApp As Application

Private Sub myApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveWorkbook.Name = "TestBookB.xlsx" And ActiveSheet.Name = "WorkbookB" Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then
            MsgBox "You clicked me"
        End If
    End If
End Sub

In the ThisWorkbook code:

' in Workbook A ThisWorkbook code module

Dim myApplication As Class1

Private Sub Workbook_Open()
Workbooks.Open ("C:\Users\Owner\Documents\TestBookB.xlsx")
    Set myApplication = New Class1
    Set myApplication.myApp = ThisWorkbook.Parent
End Sub

This structure will actually work very well to make sure I am loading the right forms on the right spreadsheets.

Thank you so much mikerickson! With hardly any modifications, you got me exactly what I needed and put to rest a night's worth of fruitless Googling.

 

[mikerickson]

I try to avoid using ActiveSheet or ActiveCell in SelectionChange events.

Private Sub myApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Parent.Name = "TestBookB.xlsx" And Sh.Name = "WorkbookB" Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then
            MsgBox "You clicked me"
        End If
    End If
End Sub

 

[JonXL]

Even better!

Thank you again.

 

[JonXL]

After finishing all the forms and making the necessary changes to fire the right forms on the right spreadsheets, everything is looking great.

But now I have one more requirement and that is to close down the class1 module when the user closes the B workbook. I can accomplish this in a standard module Sub that closes the book and sets myApplication to nothing, but I can't use the logic above to fire this automatically when B is closed through the regular interface.

I added a myApp_Workbook_BeforeClose to the class module but even without the tests to check which book has closed, it never fires.

I'm missing something but don't know what.

Any additional help is really appreciated.

Thank you.

 

[Jaafar Tribak]

I think in your specific scenario, it would probably be better and easier not to use application level events ... I would hook the opened workbook into the workbook events as follows :

Code in the Thisworkbook Module of workbook A:

Option Explicit

Private WithEvents wb As Workbook

Private Sub Workbook_Open()
    Set wb = Workbooks.Open("C:\Users\Owner\Documents\TestBookB.xlsx")
End Sub

Private Sub wb_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "You selected : " & vbCr & vbCr & _
    "Cell: '" & Target.Address & "'" & vbCr & "In Sheet: '" & Sh.Name & "'" & vbCr & _
    "Of workbook: '" & wb.FullName & "'"
End Sub

This should affect exclusively the newly opened workbook as opposed to using Application level events which affects all workbooks hence avoiding the issues you described.