Hello Excellers, time for another #macromondays blogpost. Today lets look at a really simple macro which will display a message box to the user with a count of how many Excel workbooks are open.
Related Blog Posts.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
What Does The Macro Do?
This Macro uses the Workbooks.Count property which returns the number of open Excel Workbooks. For ease, we then get Excel to display the result of this in a handy message box using the MsgBox Function.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Right Click on personal.xbl in the Project Window. I have stored this macro in my Personal Macro Workbook, so it is available to use whenever I start Excel and can apply it to any Excel workbook I choose to.
Step 2. We use the message box function in this example to DISPLAY information to the user. The syntax of the MsgBox is simple
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
The only required part of the syntax is the prompt. All others are optional. Indicated by the [ ] brackets. In this example it is the result of the Workbooks.Count calculation.
Step 3. Once the MsgBox has displayed the value, then Macro ends.
Step 4. Test Your Macro!
This is a really short macro, but surprisingly handy. We could go a little further and customise the message box. We have only just used the minimum and required part of the function. So we can go ahead and type a title to be displayed to give a little more context for the user.
By amending the code slightly and adding in the Title part of the code,we can add and display some context. When we run the macro again, it makes a little more sense. ( You could type any text for your users to see in the Title Part of the MsgBox function).
Want To Copy The Code?
‘macro by howtoexcelatexcel.com
MsgBox Workbooks.Count, , “The Number Of Workbooks Open….”