CODEWITHSUNDEEP
vbaexcel
Mr. J
Mr. J

Reputation: 317

Personal VBA code Workbook - BeforeClose event across all/any open workbook

I have written a Workbook_BeforeClose event in my PERSONAL.XLSB that I would like to apply to all/any open workbooks.

The code is designed to look for a specific string of text in the workbook title and flag a message box if the string of text is found.

Public Sub Workbook_BeforeClose(Cancel As Boolean)
Dim pos As Integer
Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String

' Test
pos = InStr(ThisWorkbook.Name, "<Insert String here>")

If pos <> 0 Then

    ' Prompt
    strPrompt = "<Message>"

    ' Dialog's Title
    strTitle = "<Title>"

    ' Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)

    ' Check pressed button
    If iRet = vbNo Then
        Cancel = True
    Else
    End If

End If

End Sub

I appreciate that because this macro lies within my PERSONAL.XLSB workbook that it applies solely to itself only.

I am looking for a solution where a personal before close macro could apply to all/any open workbooks that do not themselves contain macros.

Upvotes: 0

Views: 1769

Answers (1)

nbayly
nbayly

Reputation: 2167

This can be accomplished by trapping the Application event rather than the Workbook event.

Source: http://www.mrexcel.com/forum/excel-questions/488682-automatically-run-workbook-event-workbook-open.html

Private WithEvents AppEvents As Application

Private Sub Workbook_Open()

    Set AppEvents = Application

End Sub

Private Sub AppEvents_WorkbookBeforeCloseEventHandler(ByVal Wb As Workbook)

    If Not Wb Is Me Then
        Call YourMacro(Wb)
    End If

End Sub

Just encapsulate your code in a Sub and call it where "YourMacro" is indicated.

Hope this helps. Cheers.

Upvotes: 0

Related Questions