VBA - automatically run when any workbook is opened

Question

I want a Sub in my PERSONAL.XLAM file to run every time any workbook is opened. The following works, but only when opening a workbook when no other workbook is open.

Private Sub Workbook_Open()
MsgBox "Hello."
End Sub

How can I make this work upon opening additional workbooks?

you could write a code in the Auto_Open sub of each workbook in question maybe ? I am not sure I fully understand your question... and to make sure you don't try to open it more than once you can verify if it is open or not before doing so — Seb
– Seb, Commented May 12, 2017 at 14:36
I want a macro to automatically run on every single workbook that is opened as soon as it is opened. There are going to be thousands of different workbooks, so I can't add them to each workbook. — Robby
– Robby, Commented May 12, 2017 at 14:38
You can't:( Personal.xlam (or any other file) can't see when other files are opened. It works for you for the first opening only because that time personal.xlam is opened too. The only way is to place a code in all of your workbooks. — Máté Juhász
– Máté Juhász, Commented May 12, 2017 at 14:44

Community · Accepted Answer · 2018-05-08 21:49:49Z

5

As per the comment from Masoud, this is a great article. You can find all the detail there, but simply, this is all you need:

Private WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "New Workbook: " & Wb.Name
End Sub

Place the code into your ThisWorkbook module.

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered May 12, 2017 at 23:29

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - automatically run when any workbook is opened

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related