How to detect workbook New openings (vba)

[kelly mort]

𝙷𝚎𝚕𝚕𝚘 𝚠𝚘𝚛𝚕𝚍

𝙵𝚘𝚛 𝚎𝚎𝚡𝚊𝚖𝚙𝚕𝚎, 𝚒𝚗 𝚊𝚌𝚌𝚎𝚜𝚜 𝚢𝚘𝚞 𝚌𝚊𝚗 𝚘𝚙𝚎𝚗 𝚓𝚞𝚜𝚝 𝚘𝚗𝚎 𝚏𝚒𝚕𝚎 𝚊𝚝 𝚊 𝚝𝚒𝚖𝚎.

𝙸 𝚊𝚖 𝚝𝚛𝚢𝚒𝚗𝚐 𝚝𝚘 𝚊𝚊𝚌𝚑𝚒𝚎𝚟𝚎 𝚝𝚑𝚊𝚝 𝚒𝚗 𝚎𝚡𝚌𝚎𝚕.

𝚜𝚘 𝚠𝚑𝚎𝚗 𝚒 𝚊𝚖 𝚘𝚙𝚎𝚗𝚒𝚗𝚐 𝚖𝚢 𝚏𝚒𝚕𝚎 𝚒 𝚌𝚊𝚗 𝚌𝚘𝚞𝚗𝚝 𝚝𝚑𝚎 𝚗𝚗𝚞𝚖𝚋𝚎𝚛 𝚘𝚏 𝚘𝚙𝚎𝚗𝚎𝚍 𝚠𝚘𝚛𝚔𝚋𝚘𝚘𝚔𝚜 𝚝𝚑𝚎𝚗 𝚌𝚕𝚘𝚜𝚎 𝚖𝚢 𝚏𝚒𝚕𝚎 𝚘𝚛 𝚘𝚝𝚑𝚎𝚛𝚠𝚒𝚜𝚎

𝚃𝚑𝚎 𝚘𝚗𝚕𝚢 𝚒𝚜𝚜𝚞𝚎 𝚒 𝚑𝚊𝚟𝚎 𝚗𝚘𝚠 𝚒𝚜 𝚑𝚘𝚠 𝚝𝚘 𝚍𝚎𝚝𝚎𝚌𝚝 that when I am trying to open a different file while my file with the vba code is opened

If there is a way, I will love to know.

 

[Domenic]

You'll need to use an application-level event handler. Try the following...

[Class module]

VBA >> Insert >> Class module, and name it clsApp.

Option Explicit

Public WithEvents xl As Application

Private Sub xl_WorkbookOpen(ByVal Wb As Workbook)
    If Not Wb Is ThisWorkbook Then
        MsgBox "WorkbookOpen, " & Wb.Name
        'your code here
        '
        '
    End If
End Sub

[Regular module]

VBA >> Insert >> Module

Option Explicit

Dim xlApp As clsApp

Sub init()

    Set xlApp = New clsApp
  
    Set xlApp.xl = Application
  
End Sub

[ThisWorkbook module]

Option Explicit

Private Sub Workbook_Open()
    init
End Sub

Then save the workbook, close and re-open it.

Hope this helps!

 

[kelly mort]

Okay nice
It is getting closer

It works fine with already saved or created files but when i create new workbook the message alert does not show up

 

[Domenic]

For a new workbook, you'll need to add another event handler. So your class module should now look something like this...

Option Explicit

Public WithEvents xl As Application

Private Sub xl_NewWorkbook(ByVal Wb As Workbook)
    If Not Wb Is ThisWorkbook Then
        MsgBox "NewWorkbook, " & Wb.Name
        'your code here
        '
        '
    End If
End Sub

Private Sub xl_WorkbookOpen(ByVal Wb As Workbook)
    If Not Wb Is ThisWorkbook Then
        MsgBox "WorkbookOpen, " & Wb.Name
        'your code here
        '
        '
    End If
End Sub

 

[kelly mort]

Excellent!

Thanks so much @Domenic

You nailed it

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!