How to update title bar to display full file path when document is saved?

Special K

Board Regular
Joined
Jun 20, 2011
Messages
75
Is it possible to update the title bar to display the current file's full path whenever the document is saved?

Thanks to the replies in an earlier thread I created, I already have a function to display the full file path in the title bar when an existing file is opened:


The above thread references both Excel 2010 and Excel 2013; apparently they behave differently with this functionality. For the purposes of this thread, I am using Excel 2010.

The problem is that if I open a new instance of Excel, create a new file, then save it, the title bar does not display the path of the newly-created file because the subroutine only triggers when the workbook is opened. Here is what I have so far:

VBA Code:
Option Explicit

' code to automatically display the full file path in the Excel title bar
' copied from here: https://www.mrexcel.com/board/threads/possible-to-always-display-full-file-path-in-title-bar-excel-2010.1206961/#post-5895948
' might need to mark this file as 'read only' to avoid getting "open for editing" errors when opening multiple instances of Excel: https://www.systemroot.ca/2013/08/how-to-fix-file-in-use-personal-xlsb-is-locked-for-editing/

Private WithEvents xlsApp  As Application

Private Sub Workbook_Open()
    Set xlsApp = ThisWorkbook.Application
End Sub

Private Sub xlsApp_WorkbookOpen(ByVal Wb As Workbook)
    ActiveWindow.Caption = Wb.FullName
End Sub

' new code to have the title bar updated whenever the document is saved; this ensures newly-created documents will have their paths displayed
' in the title bar once they are saved

Private Sub Workbook_Save()
    Set xlsApp = ThisWorkbook.Application
End Sub

Private Sub xlsApp_WorkbookSave(ByVal Wb As Workbook)
    ActiveWindow.Caption = Wb.FullName
End Sub

The problem is that the title bar doesn't change when I save a new document. It just displays the full path to my personal.xlsb file, because that file is automatically loaded every time I launch Excel. I would expect the title bar to update and display the full path of the newly-saved file. What am I doing wrong here? I just copied the code from the Workbook_Open example and pasted it into a Workbook_Save subroutine, but I suspect there is a gap in my understanding of how this code works.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,337
Office Version
  1. 365
Platform
  1. Windows
As a general rule, you shouldn't write the event signature code yourself (when you just copied the Workbook_Open example) - because each event will have a particular signature that needs to be used. Best to let the VBA Editor generate it for you - just use the two comboboxes about the code editor:

1658888741887.png


The following code, by way of example, also puts your code in both the WorkbookBeforeSave and WorkbookAfterSave events - you don't need to use it for both, as below - it's just an example to show that there are even differences again in the event signatures between seemingly similar evetns. Also, you likely don't need to use ThisWorkbook.Application - just Application is fine. Try:

VBA Code:
Private WithEvents xlsApp  As Application

Private Sub Workbook_Open()
    Set xlsApp = Application
End Sub

Private Sub xlsApp_WorkbookAfterSave(ByVal Wb As Workbook, ByVal Success As Boolean)
    Debug.Print Wb.FullName
    ActiveWindow.Caption = Wb.FullName
End Sub

Private Sub xlsApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Debug.Print Wb.FullName
    ActiveWindow.Caption = Wb.FullName
End Sub

Private Sub xlsApp_WorkbookOpen(ByVal Wb As Workbook)
    Debug.Print Wb.FullName
    ActiveWindow.Caption = Wb.FullName
End Sub

Hope that helps. Let us know how it goes.
 
Solution

Special K

Board Regular
Joined
Jun 20, 2011
Messages
75
As a general rule, you shouldn't write the event signature code yourself (when you just copied the Workbook_Open example) - because each event will have a particular signature that needs to be used. Best to let the VBA Editor generate it for you - just use the two comboboxes about the code editor:

Just to clarify, by "signature" you mean the exact syntax of the subroutine declaration, correct? That is, the code will still work if I manually type it in exactly as quoted in your message, it's just easier to use the built-in combo boxes in the editor because that will ensure the syntax is always correct? I'm assuming my original code didn't work because Workbook_Save() isn't a defined event (i.e. it's not listed in the combo box).

Also your code does what I wanted, thanks.
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,337
Office Version
  1. 365
Platform
  1. Windows
Just to clarify, by "signature" you mean the exact syntax of the subroutine declaration, correct? That is, the code will still work if I manually type it in exactly as quoted in your message, it's just easier to use the built-in combo boxes in the editor because that will ensure the syntax is always correct? I'm assuming my original code didn't work because Workbook_Save() isn't a defined event (i.e. it's not listed in the combo box).

Also your code does what I wanted, thanks.
Yes to both questions, and yes to your final observation that _Save is not a defined event.

Glad it's working.
 

Forum statistics

Threads
1,176,119
Messages
5,901,483
Members
434,896
Latest member
Derquila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top