Quick Jump
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.

Example

Update Title Bar to show when you opened your file

Private Sub Workbook_Open()
Application.Caption = Now()
End Sub

Tutorial

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”

Default Application Caption
Microsoft Excel

Fortunately, Microsoft so graciously granted us the power to change the caption however we please! Follow these steps:

  1. Save your Excel file as a macro-enabled workbook (.xlsm extension)
  2. Open your VBA Editor
  3. Click This Workbook in your Project Explorer window
  4. Click the dropdown menu beside “(General)”
  5. Select Workbook
    ThisWorkbook Workbook dropdown

  6. Select Open from your “(Declarations)” dropdown menu
    ThisWorkbook Workbook_Open

  7. Paste Application.Caption = Now() inside the Workbook_Open private subroutine
  8. Save your workbook
  9. Close your workbook
  10. Open your workbook

Your title bar now shows what time you opened your file instead of showing “Microsoft Excel!” Pretty neat, eh?

New Application Caption
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!

Application Ideas

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?

  1. Count how many times a user presses a button and display the results in the caption
  2. Leave your mark by placing your name in the caption
  3. Show your employer you’re a real “company man” by displaying your company’s name in the title bar
  4. Display the name of the last executed macro
  5. Update your user with the status of your macro execution by changing the caption to “Running” or “Acquiring Data.”
  6. Query for the username of the person logged in and greet the person with a custom message, like “Good morning, Ryan”
  7. Check the current time and say “Good morning” if before noon or “Good afternoon” if after noon.

Comments

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.


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


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.