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
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 friend, submit a comment below and follow me on Google+! 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.
Coming Fall 2017
Auto-Import Macros Directly from wellsr.com
Absolutely FREE when you join the waitlist
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow