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

Users are saying about us...

  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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube