• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change Caption on Save as

nagovind

Member
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

Excel Ninja
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

Member
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
 
Top