Hello Excellers, time for some more #macromonday fun. I am excited to share with you some more Excel VBA code. Have you ever needed to know how many worksheets are in a workbook, specifically how many hidden or visible sheets there are? If so, then this Excel macro is for you! This macro counts allows you to count hidden and also the visible worksheets in Excel. Yes, the number of worksheets in a workbook, both hidden and visible. It’s easy to use – just run it from the Developer tab. So give it a try and see how useful it can be!
I had this exact problem recently at work. I needed to know quickly how many worksheets were in my Excel workbook. More worksheets could be in the workbook than could be seen. So potentially, there may be hidden worksheets. But it took a lot of time to go through and count them, and it is easy to get it wrong when there are a lot of worksheets.
So, let’s write some code, and tuck it away in our Personal Macro Workbook and we can re use that handy piece of code whenever we need it. Let’s count how many worksheets there are, and display that number in a message box. Easy!
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, 50 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.
How To Excel At Excel – Macro Mondays Blog Posts.
What Does The Macro Do?
This Macro uses the ActiveWorkbook.sheets.count property which returns the number of objects (in this example worksheets) in the collection. For ease, we then get Excel to display this in a handy message box using the MsgBox Function. This method can be used to count hidden and visible worksheets in Excel.
Preparing To Write The Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
This macro will be useful in the future so I am going to save it in my Personal Macro Workbook.
More About Your Personal Macro Workbook (PMW).
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Step 1. Inserting A New Visual Basic Module And Naming The Macro
Open Visual Basic – by hitting ALT +F11 or Developer Tab – Right Click on personal.xbl in the Project Window. I have We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply CountMySheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We need to enter the rest of the code between these two lines.
Step 2. Using The Messge Box Function
Next, 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 in this example. All others are optional. Indicated by the [ ] brackets. In this example it is the result of the ActiveWorkbook.sheets.count calculation.
Step 3. Displaying The Count Of Worksheets In The Excel Workbook.
Once the user runs the macro, the number of visible and hidden worksheets are displayed in the message box.
Step 4. Ending The Macro After Excel Counts The Worksheets.
Once the MsgBox has displayed the value, then Macro ends.
There are 21 worksheet in my workbook. This includes both visible and hidden. Thanks for the quick count of my Excel worksheets Excel.!
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. let’s 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/argument of the code some context is added. 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).
Sub CountMyWorkSheets()
MsgBox ActiveWorkbook.sheets.Count, , "The Number Of Sheets In Your Workbook Is"
End Sub
The result is seen below, more content to the user.
Do You Want To Copy The Code. Count your Excel Worksheets.
Feel free to copy the code. Use it the Count how many worksheets are in you Excel workbook.
Sub CountMyWorksheets()
MsgBox ActiveWorkbook.sheets.Count, , "The Number Of Sheets In Your Workbook Is"
End Sub
