View Single Post
Old 07-30-2022, 01:12 PM
Jamal NUMAN Jamal NUMAN is offline Windows 7 64bit Office 2010 64bit
Join Date: Nov 2010
Posts: 508
Jamal NUMAN is on a distinguished road
Question Display workbook filename in title bar and use personal workbook

Originally Posted by macropod View Post
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'.
  MultiUse = -1  'True
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:
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?
Attached Images
File Type: jpg Clip_985.jpg (103.4 KB, 9 views)
Reply With Quote