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

[Special K]

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:

Possible to always display full file path in title bar (Excel 2010)?

I am using Excel 2010 and the base filename is displayed in the window title bar. Is it possible to have the full path displayed without having to save every workbook as .xlsm (i.e. macro-enabled workbook)? I've found a couple possible solutions to my question online but they all involve macros...

www.mrexcel.com

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:

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.

 

[Dan_W]

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:

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:

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.

 

[Special K]

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]

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.