Monitor next workbook to open

[TangentialAccel]

Hi guys,
<o></o>
I’m trying to write VB code in workbook A that monitors the next workbook to open (we’ll call it workbook B) and reports back to workbook A whether or not Workbook B is read-only or not. Workbook B could be a VBA enabled or a non-VBA enabled workbook and could be any workbook opened by the user, so the file path and file name wouldn’t be known. I would like Workbook A to report a Boolean value – True if workbook B is read-only, and False if workbook B is not read-only. Below is what Ihave so far, but I know that I’m missing something... I'm using Excel 2007 btw.
<o></o>

'Code in ThisWorkbook object in Workbook A:
Public ro As Boolean

Private Sub workbook_activate()
If ActiveWorkbook.ReadOnlyRecommended = True Then
 MsgBox "This workbook is saved as read-only recommended"
 ro = True
Else
 ro = False
End If
End Sub

Thanks in advance!<o></o>

 

[VBA Geek]

add this code in the THISWORKBOOK code module (in workbook A). Then save, close and reopen workbook A and try to open another workbook

 Dim WithEvents oApp As Excel.Application

Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "The Excel File: " & Wb.Name & " is: " & IIf(Wb.ReadOnly, "Read Only", "Not Read Only")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set oApp = Nothing
End Sub

Private Sub Workbook_Open()
    Set oApp = Excel.Application
End Sub

 

[Fluff]

How about

Option Explicit

'Code in ThisWorkbook object in Workbook A:
Public ro As Boolean

Private Sub Workbook_Deactivate()
   If ActiveWorkbook.ReadOnlyRecommended = True Then
    MsgBox "This workbook is saved as read-only recommended"
    ro = True
   Else
    ro = False
   End If
MsgBox ro

End Sub

 

[TangentialAccel]

Thank you!

 

[Fluff]

Glad we could help