ThreeWave Full File Name In Window Caption

This page describes how to put the full file name in the application and workbook window captions.
ShortFadeBar

Introduction

As a matter of personal preference, I like to see the full file name, qualified with drive and path information, in the caption of the Excel application window and, if a window is not minimized or maximized, in the caption of a worksheet window. This can be done with just a few lines of code in the ThisWorkbook code module. It implements Application-Level Events to detect when a window is activated.

In order to work for all windows of all workbooks, you need to place the code in a workbook that is always open when you are working in Excel. The Personal.xls workbook is well suited to this. Most people has a Personal.xls that opens when Excel is started, but remains hidden. The most common use of Personal.xls is to store VBA procedures that you want always available, but it is also perfectly suited to the task here at hand.

SectionBreak

The Code

Open the VBA Editor (ALT F11) and there, open the Project Window (CTRL R). Find your Personal.xls workbook project and expand the treeview nodes under that project. Under the "Microsoft Excel Objects" item, you should see an icon named "ThisWorkbook". Double-click that and paste the following code in the code module. 

Private WithEvents App As Excel.Application

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    Dim S As String
    If Wb.FullName <> vbNullString Then
        Application.Caption = Wb.FullName
        Wn.Caption = Wb.FullName
    End If
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

Now, save the Personal.xls workbook, and then close and restart Excel. You should see the full workbook file name in the caption of the Excel Application Window as well as the individual sheet windows, as long as they are not minimized or maximized.

NOTE: The procedures and code described here will work in Excel 2007 and 2010, even thoung the personal workbook may be named Personal.xlsm or Personal.xlsb. No changes are required to support these versions of Excel.

ShortFadeBar
LastUpdate This page last updated: 9-April-2010.

-->