Show Time in Excel Title Bar with Application.Caption

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

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

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”

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
   
   
   
6. Select Open from your “(Declarations)” dropdown menu
   
   
   
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?

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!

How to change the title bar in Office 365

If you’re using Office 365 desktop, Application.Caption might not work or it might hide everything after the dash. A reader, Mitch, pointed out that O365 users can use ActiveWindow.Caption, instead. You’ll need to format the time if that’s what you’re wanting to display, like this:

ActiveWindow.Caption = ActiveWorkbook.Name & " - " & Format(Now(), "dddd MMM YYYY-hh:nn AM/PM")

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.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.