Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-30-2022, 01:12 PM
Jamal NUMAN Jamal NUMAN is offline Display workbook filename in title bar and use personal workbook Windows 7 64bit Display workbook filename in title bar and use personal workbook Office 2010 64bit
Expert
Display workbook filename in title bar and use personal workbook
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Question Display workbook filename in title bar and use personal workbook

Quote:
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'.


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?
Attached Images
File Type: jpg Clip_985.jpg (103.4 KB, 9 views)
Reply With Quote
  #2  
Old 07-30-2022, 01:35 PM
Charles Kenyon Charles Kenyon is offline Display workbook filename in title bar and use personal workbook Windows 11 Display workbook filename in title bar and use personal workbook Office 2021
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

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
Reply With Quote
  #3  
Old 07-31-2022, 08:11 AM
Jamal NUMAN Jamal NUMAN is offline Display workbook filename in title bar and use personal workbook Windows 7 64bit Display workbook filename in title bar and use personal workbook Office 2010 64bit
Expert
Display workbook filename in title bar and use personal workbook
 
Join Date: Nov 2010
Posts: 519
Jamal NUMAN is on a distinguished road
Default

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

------------------
Attached Images
File Type: jpg Clip_987.jpg (95.8 KB, 6 views)
File Type: jpg Clip_988.jpg (106.6 KB, 6 views)
File Type: jpg Clip_989.jpg (102.8 KB, 6 views)
Reply With Quote
  #4  
Old 08-04-2022, 02:56 AM
Guessed's Avatar
Guessed Guessed is offline Display workbook filename in title bar and use personal workbook Windows 10 Display workbook filename in title bar and use personal workbook Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:48 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft