Check out Application Events, especially:
WorkbookActivate
WorkbookBeforeClose
Using Application Events requires a bit of prep code
In the ThisWorkbook Code page
Option Explicit
Public WithEvents ThisApp As Application
Private Sub Workbook_Open()
Set ThisApp = Me.Application
Call Sub_to_Hide_ThisWorkbook 'Needs to be separate sub, not in the Workbook_Open sub
Call SubTo_Show_UserForm 'Needs to be separate sub, not in the Workbook_Open sub
'The rest of your sub code
End Sub
Private Sub ThisApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Not Wb Is Me Then
Call Sub_to_Hide_ThisWorkbook
Call SubTo_Show_UserForm
End If
End Sub