|
|
|
|
|
|
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:
|
- Right click on the Excel Icon to the left of "File" on the menu bar. Choose view code. Paste the code in the panel.
- 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:
|
- 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 235 times.
|
|