Update Application.Caption on worksheet change

Question

Hello, I wanted to have the current worksheets cell value displayed in the Application.Caption and have it updated with every worksheet that I go to within that workbook. Also, If I opened the workbook would it display the current worksheets caption or would it need refreshed or something.

Application.Caption = Range("P9")

ActiveWorkbook.Windows(1).Caption = Range("C6")

Thanks!

Answer 1

You will need to code for the Workbook object rather than the individual Worksheet objects. You will alos require multiple routines to cover various scenarios.

Tap Alt+F11 and when the VBE opens, right click ThisWorkbook from the Project Explorer (upper-right) and select View Code. Paste the following into the new pane titled something like Book1 - ThisWorkbook (Code),

Private Sub Workbook_Open()

On Error Resume Next

Application.Caption = ActiveSheet.Range("P9")

ActiveWorkbook.Windows(1).Caption = ActiveSheet.Range("C6")

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

On Error Resume Next

Application.Caption = Sh.Range("P9")

ActiveWorkbook.Windows(1).Caption = Sh.Range("C6")

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next

If InStr(1, " $C$6 $P$9 ", Target.Address) > 0 Then

On Error Resume Next

Application.Caption = Sh.Range("P9")

ActiveWorkbook.Windows(1).Caption = Sh.Range("C6")

End If

End Sub

I've added On Error Resume Next to cover a wide variety of scenarios where illegal characters may be assigned to the application window or active worksheet window caption. Tap Alt+Q to return to your worksheet.

You may want to use a self-signed certificate (Sign your own macros for stronger security) and digitally sign your code to avoid macro warnings on Workbook_Open().

Answer 2

Enter the following in the ThisWorkbook code module

=============================================

Private Sub Workbook_Open()

UpdateAppCaption

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

UpdateAppCaption

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

UpdateAppCaption

End Sub

Sub UpdateAppCaption()

Application.Caption = " "

ThisWorkbook.Windows(1).Caption = ActiveCell.Value

End Sub

===========================================

Answer 3

Can you explain this line? Thanks...

If InStr(1, " $C$6 $P$9 ", Target.Address) > 0 The

The InStr() VBA function finds a string inside another string and return the position or 0 (i.e. zero) if not found. Target.Address will return the absolute cell address (e.g. $A$1) so I am asking if the cell that changed is either C6 or P9. I've included spaces so that C61 or P999 do return a false positive. There is no point in running the code on the active worksheet unless C6 or P9 has changed. Opening the workbook and switching between worksheets are handled by other functions.

I chose this method as this is a Workbook function and not a Worksheet function. For a Worksheet_Change I would have used Intersect() to check.

Answer 4

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next

If InStr(1, " $C$6 $P$9 ", Target.Address) > 0 Then

On Error Resume Next

Application.Caption = Sh.Range("P9")

ActiveWorkbook.Windows(1).Caption = Sh.Range("C6")

End If

End Sub

 Can you explain this line? Thanks...

If InStr(1, " $C$6 $P$9 ", Target.Address) > 0 The