Always Execute VBA When ANY File Opened/Closed

[robshobs]

For many of the files I create, I have a worksheet, always named the same, with "data" that I hide from the user. I have built a macro that will unhide a very hidden sheet and will make a visible sheet as very hidden. I do not want the users to have access to this bit of VBA so I store it in Personal.XLS. Then I have to execute the macro myself for each file. Not really a big deal as I assigned it to an icon. However, sometimes I forget to hide my data and would like to automate it.

I would like to have the macro execute ANY time a file is opened. The WORKBOOK OPEN event will only work if the VBA is in each file, which I do not want.

Is there anyway to have VBA execute from Personal.XLS each time a file is opened?

If there is, what VBA will execute, from Personal.XLS, when a file is closed?

Thank you for your suggestions.

Rob

 

[Jaafar Tribak]

You could try something along these lines in the Personal.xls workbook module :

Option Explicit

Private WithEvents oApp As Application

Private Sub Workbook_Open()
    Set oApp = Application
End Sub

Private Sub oApp_NewWorkbook(ByVal Wb As Workbook)
    Call DisplayMsg(Wb, Opening:=True)
End Sub

Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
    Call DisplayMsg(Wb, Opening:=True)
End Sub

Private Sub oApp_WorkbookBeforeClose _
(ByVal Wb As Workbook, Cancel As Boolean)
    Call DisplayMsg(Wb, Opening:=False)
End Sub

Private Sub DisplayMsg(ByVal Wb As Workbook, ByVal Opening As Boolean)
    Dim sMsg As String
    sMsg = IIf(Opening, "  is opening", "  is Closing")
    If Not Wb Is Me Then
        MsgBox Wb.Name & sMsg
    End If
End Sub

 

[robshobs]

You could try something along these lines in the Personal.xls workbook module :

Jaafar - this is great. Thank you very much!

Quick note to others. First, make sure you put it in the WORKBOOK module on Personal.xls. Second, it won't start working until after Excel is closed and reopened.