Excel

Full path and sheet name in Windows caption

Ease of Use

Easy

Version tested with

2003 

Submitted by:

lenze

Description:

This code will place the path and sheet name in the active window 

Discussion:

Placing the path and sheet name in a cell is a very common question on Excel boards. Usually it is to provide a printout of the path and name. This, of course, can be done in the footer with a Before_Print Event. This code will place the path and sheet name in the caption area of the active window 

Code:

instructions for use

			

In the ThisWorkbook module Option Explicit Private Sub Workbook_Open() Application.ActiveWindow.Caption = Me.FullName & "\" & ActiveSheet.Name End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Application.ActiveWindow.Caption = Me.FullName & "\" & ActiveSheet.Name End Sub

How to use:

  1. Right click on the Excel Icon to the left of "File" on the menu bar. Choose view code. Paste the code in the panel.
  2. If you want to have it available for all new workbooks, place it in a blank workbook and save it as Book.xlt in your xlStart folder.
 

Test the code:

  1. Note, after pasting the code in the module, you should close and save the file. When you reopen it, the code will run. Also, the code will change the sheet name in the caption when a new sheet is activated.
 

Sample File:

Caption_Example.zip 7.32KB 

Approved by mdmackillop


This entry has been viewed 232 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express