Change Caption on Save as

[nagovind]

Dear all


I'm using this code to display the caption as ...as desired by me..so that Microsoft Excel will disappear..it is working


but it is not updating after i save the file as using Save as


how to code ..such that the NEW file name concatenated with my name has to be updated


ActiveWindow.DisplayHeadings = False

Application.DisplayFormulaBar = False


Windows(1).Caption = "[ & ThisWorkbook.Name & ]"


Application.Caption = "My Name"

 

[Luke M]

With the code you already posted contained within a macro that is in a regular module (I'll assume it's called UpdateName), you can put the following in the ThisWorkbook module:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.OnTime Now + TimeValue("00:00:01"), "UpdateName

End Sub


What it does is schedule the UpdateName macro to wait at least 1 second after now (giving Excel time to display the save as dialogue and take over), and then run at first chance.


WARNING: This could cause strange effects if you do a Save & Close.

 

[nagovind]

Luke M,


I tried but not working


If you save the file using save as..then the file name will be Inside [ ] but it is not updating as per new file name after save as...


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.OnTime Now + TimeValue("00:00:01"), "update"

End Sub


Sub update()

Windows(1).Caption = "[ & ThisWorkbook.Name & ]"

End Sub

 

[Luke M]

Are you sure you put the code in the ThisWorkbook module?