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]

What changes would be needed if I want to open book B from a macro in a standard module? I don't want it opening with book A. I was able to figure out how to modify mikerickson's code to do this...

 

[Jaafar Tribak]

What changes would be needed if I want to open book B from a macro in a standard module? I don't want it opening with book A. I was able to figure out how to modify mikerickson's code to do this...

1- Code in the Thisworkbook Module of Workbook A :

Option Explicit

Public WithEvents wb As Workbook

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

Private Sub wb_BeforeClose(Cancel As Boolean)
    Set wb = Nothing
End Sub

2- Code in a Standard Module:

Option Explicit

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

 

[JonXL]

That did the trick splendidly.

Thank you!