How to Create a Personal Macro Workbook (Video Series)
Bottom line: This video series explains what the Personal Macro Workbook is, how to create it, and why you should use one. You will also learn how to add custom macro buttons to the Excel Ribbon, and how to make a Yes/No pop-up window appear before a macro runs.
Skill level: Intermediate
Are You Turning the Clocks Back (or Forward)?
If you live in a country that observes Daylight Savings Time (over 70% of countries do), then this is the time of the year that the clock shifts an hour.
I live in the northern hemisphere (U.S.), and we lost an hour this past weekend. Technically the clock shifts in the middle of the night while you are sleeping, but I think that you really lose that hour over the course of an entire week with every yawn and eye rub you do. 😉
I don't know about you, but the “Fall back” time change always makes me a little sleepier for a few days.
On a side note, my wife and I use a wake-up light (amazon link) instead of an alarm clock, and wake up in a much better mood these days. I still yawn in the morning, but it is a lot less grumpy. 🙂 If you get up before the sunrise then you might want to check it out.
The Personal Macro Workbook can Save You Hours!
The time change has inspired me to help you save an hour out of every work week.
How? With the Personal Macro Workbook.
You probably already know that macros can help automate routine Excel tasks to save you time. This is one feature that makes Excel awesome!
The Personal Macro Workbook is just an Excel workbook that stores all your macros in one place.
The nice part is that it opens in the background every time you open Excel. This makes it easy to run your macros at any time.
I like to think of it as your Excel tool belt. Ladies, you can think of it as your Excel purse. 🙂 The Personal Macro Workbook is always at your side, ready to assist you with common daily tasks.
These are usually simple tasks like refreshing all the pivot tables or creating a list of all the sheets in the workbook. But you can store any macro, function, or userform in your personal macro workbook.
Video Series on the Personal Macro Workbook
I have put together a video series that explains everything you need to know to get started with your own Personal Macro Workbook.
I even explain how to record your first macro, and I have additional free training videos if you are new to macros and VBA.
This 4-part video series includes:
Video 1: How to create the Personal Macro Workbook, and why you should use it.
Video 2: Where the Personal Macro Workbook file is stored on your computer and how to view it.
Video 3: How to customize the Excel Ribbon and Quick Access Toolbar to add your own custom macro buttons.
Video 4: How to make a Yes/No message box (pop-up) window appear before the macro runs, critical to preventing disaster…
In the videos I mention my free training series on Macros & VBA. That video series covers everything to get you started writing your own macros and automating tasks in Excel.
Download the File
You can download the file I used in the videos below. This file contains the code for the Yes/No Message Box and a few other macros.
PERSONAL - Excel Campus.xlsb (22.4 KB)
What Macros Are In Your Personal Macro Workbook?
If you are already using a personal macro workbook, then you might pick up some tips from the video series.
However, I want to know what macros you are using every day. Please leave a comment below with a description, or the code for the macros you can't live without.
If you are looking for a macro that can automate a common task, but not sure how to create it, leave a comment below as well. My hope is that this page can be a place where we share our macros and learn from each other.
Note: When leaving a comment below with your code, you can paste the code in the comment box and wrap it the <pre>pasted VBA code</pre> tags. This will keep all the indentations and line breaks from the VB Editor.