Introduction | Example | Tutorial | Applications | Comments
Introduction - Application.Caption
Instead of displaying “Microsoft Excel,” show something useful in your Excel title bar! Use the Application.Caption property to display the exact time you opened your Excel workbook. A little VBA goes a long way toward making you and your Excel workbooks stand out. This is great for company programs or for showing off to your friends.
Update Title Bar to show when you opened your file
Private Sub Workbook_Open() Application.Caption = Now() End Sub
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.
In Excel, the Application.Caption property returns a string data type and displays the string at the top of your Excel window. The default caption is “Microsoft Excel”
Fortunately, Microsoft so graciously granted us the power to change the caption however we please! Follow these steps:
- Save your Excel file as a macro-enabled workbook (.xlsm extension)
- Open your VBA Editor
- Click This Workbook in your Project Explorer window
- Click the dropdown menu beside “(General)”
- Select Workbook
- Select Open from your “(Declarations)” dropdown menu
Application.Caption = Now()inside the Workbook_Open private subroutine
- Save your workbook
- Close your workbook
- Open your workbook
Your title bar now shows what time you opened your file instead of showing “Microsoft Excel!” Pretty neat, eh?
Date and time shown in Title Bar
Note: The first time you do this, you may have a prompt asking you to enable macro content. That’s okay. Press Enable Content and you’re done.
Now that I taught you how to fish, allow me to tell you a quicker way. Instead of selecting the dropdown menus in steps 5 and 6, paste the Workbook_Open example immediately after Step 5, then jump to Step 8.
Although I changed the Application.Caption property when the file opened, you can put it in a public module and change it anytime you want. Check out the ideas below!
Make your Excel workbooks stand out by changing your application caption. Here’s a little bonus code. Tell the user when you last saved your file by updating the caption each time you click save!
Display the time you last saved your file in the title bar
Private Sub Workbook_AfterSave(ByVal Success As Boolean) Application.Caption = Now() End Sub
Looking for more ideas?
- Count how many times a user presses a button and display the results in the caption
- Leave your mark by placing your name in the caption
- Show your employer you’re a real “company man” by displaying your company’s name in the title bar
- Display the name of the last executed macro
- Update your user with the status of your macro execution by changing the caption to “Running” or “Acquiring Data.”
- Query for the username of the person logged in and greet the person with a custom message, like “Good morning, Ryan”
- Check the current time and say “Good morning” if before noon or “Good afternoon” if after noon.
If you like what you see, share this article with a friendand submit a comment below! Come back often to see more great VBA ideas.
Remember, if you or your company are struggling with VBA, just contact me and I’ll be happy to assist.