Inserting the path where the Workbook is saved to the title bar or the status bar using VBA in Microsoft Excel

 

The top line of the window is the Title bar. Use the available empty space to add metadata related to workbook.

To add metadata, we will add code to workbook activate event. So, title of the workbook will change when workbook is activated.

By default, Excel workbook features “Microsoft Excel” as title.

ArrowMain

To add a title to the Title bar:

1. Press Alt+F11 to open the VBE.

2. In the VBAProject pane, double-click ThisWorkbook.

3. At the top of the module, open the left drop down list (General) and select Workbook, then select the Activate event from the event drop down list on the right.

4. In the Activate event, type the following code:

Application.Caption = “Macro File”

Above code will assign “Macro File” as title to the workbook.

5. To cancel the displaying title while activating or opening a different workbook, add the Deactivate event by selecting Deactivate from the Procedure drop down list.

6. In the Deactivate event, type the following code:

Application.Caption=” “

ArrowOutput

 

Please follow below for the code

Option Explicit

Private Sub Workbook_Activate()
    'Changing the caption of the workbook
    Application.Caption = "Macro File"
End Sub

Private Sub Workbook_Deactivate()
    'Changing the caption of the workbook to blank
    Application.Caption = ""
End Sub

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com



Example:


8 thoughts on “Inserting the path where the Workbook is saved to the title bar or the status bar using VBA in Microsoft Excel

  1. How can have the path saved to the status bar automatically for all workbooks? When I created the macro, it was specific to the current workbook?b

  2. “Another method (works for those ‘other’ products Word and PowerPoint too) is to :
    1. Open the web toolbar.
    2. Whilst holding down CTRL and ALT, right-click on the address control and drag up to the menu bar just after the Help menu.
    You now have a copy of the filename available for all files as they are opened.

    It does not get the file name in the application name as for the original tip, but it does avoid eachfile needing to contian a macro.”

  3. I just wanna create macro to copy a figure in particular column in first sheet and to find the same number (which I copied from sheet 1) in Sheet 2. The problem I am facing is excel is creating Macro to find the specific value, but if I change the values then it is not working. Please help me.

  4. There are a number of ways to search for data in this fashion. In the macro, create a named variable, then pick up the data you want to find by storing it’s value in the variable. then use the Variable as the search criteria on the second sheet.

  5. “Hello,

    If you are looking for a great book on the subject of VBA and Macros, check out Writing Excel Macros with VBA, 2nd Edition”

  6. “Hi,

    I want to change the values of the selected region on the sheet.

    How to access the values of the same region.

    Aditya Datar “

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>