How to change the default caption of a workbook to the full pathname?

[SteveM]

The subject sums it up. I do optimization and simulation modeling
using Excel as a data management platform. But find that with
multiple versions of a model open, I sometimes work on or save/not
save the wrong version. Any visual cue I can find would be great.
For this solution I've inserted a simple line of code into the
Workbook Open sub:

Private Sub Workbook_Open()

ThisWorkbook.Application.Caption = ThisWorkbook.Path

End Sub

I then saved it as a template so new workbooks will contain it.

But is there a way to make the default the full pathname without using
a macro?

And is there a way to change my existing workbooks' caption to the
full pathname without without too much VBA trouble?

Thanks Much,

SteveM

 

[Chip Pearson]

Probably the simplest way would be to write an Add In that uses application
events to catch the WorkbookActivate and WorkbookDeactivate events. E.g.,

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
Application.Caption = Wb.FullName
End Sub

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
Application.Caption = vbNullString
End Sub

See www.cpearson.com/Excel/AppEvent.aspx for information about using
application events. See www.cpearson.com/Excel/CreateAddIn.aspx for
information about writing XLA add-ins.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[SteveM]

Probably the simplest way would be to write an Add In that uses application
events to catch the WorkbookActivate and WorkbookDeactivate events. E.g.,

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
Application.Caption = Wb.FullName
End Sub

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
Application.Caption = vbNullString
End Sub

Seewww.cpearson.com/Excel/AppEvent.aspxfor information about using
application events. Seewww.cpearson.com/Excel/CreateAddIn.aspxfor
information about writing XLA add-ins.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)

Chip, nice simple solution. Thanks for the advice.

SteveM