Display workbook filename in title bar and use personal workbook

Jamal NUMAN · #1 07-30-2022, 01:12 PM

Quote:

Originally Posted by macropod

For Excel, it's a bit more complicated. First, you need to have a 'Personal' Workbook. If you don't have one, you can create it by recording a macro and telling Excel to store in the 'Personal Macros Workbook'. You can delete the macro (and its code module) later.

Using Notepad, save the following code to a text file named 'clsAppEvents.cls'.

Code:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "clsAppEvents"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Public WithEvents App As Application
Attribute App.VB_VarHelpID = -1
Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Application.ActiveWindow.Caption = ActiveWorkbook.FullName
End Sub

Open the vba editor and select your 'Personal' workbook. Then use File|Import to import the 'clsAppEvents.cls' file.

Open your 'Personal' workbook's 'ThisWorkbook' module and the following code to it:

Code:

Option Explicit
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open() 
Set AppClass.App = Application 
 End Sub

Exit & re-start Excel. You can also now delete the 'clsAppEvents.cls' file.

I couldn’t figure out how to display the file location path in Excel.

How can I create the personal workbook?

Charles Kenyon · #2 07-30-2022, 01:35 PM

I moved this into the Excel programming forum. That way others looking for answers will be more likely to find it.
The following may help you: Copy your macros to a Personal Macro Workbook

Jamal NUMAN · #3 07-31-2022, 08:11 AM

I google it and found the solution below. However, the code needs to be run each time the excel file is open in order to get the path displayed on the title bar

------------------
Sub showCaption()
ActiveWindow.Caption = ActiveWorkbook.FullName
End Sub

------------------

Guessed · #4 08-04-2022, 02:56 AM

To get the macro to run without manually asking it to run, you need to put it in the ThisWorkbook module and use the built-in names associated with particular events. I would use these ones

Code:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  ActiveWindow.Caption = ActiveWorkbook.FullName
End Sub

Private Sub Workbook_Open()
  ActiveWindow.Caption = ActiveWorkbook.FullName
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I copy a workbook to another workbook and keep the formatting within the new workbook	excelforsue	Excel	3	09-15-2020 03:37 AM
Macro Personal Workbook does not load in Excel 2016	Intruder	Excel Programming	0	01-21-2017 05:44 AM
Personal Macro Workbook Does Not Load	WRowan	Excel	1	06-23-2016 08:26 AM
data entered in one workbook should be updated in other relevant workbook based on the date	vedha	Excel	0	04-24-2015 08:45 PM
Range(Cell1,Cell2) Error on another workbook controlling some other workbook?	tinfanide	Excel Programming	1	02-09-2012 04:08 PM