Capturing before and after file names when saving

[TimLundSE26]

In VBA, I want to be able to capture both old and new file names when I save a workbook with a new name. I can see the BeforeSave event handler, but I want a handler that is triggered after I have entered the new name in the file save dialog.

As background, what I want this for is a custom tool bar which with a submenu pop ups with selected macros from open workbooks. I want the captions for these submenus to be the same as the workbook names, so to be able to change this caption when I save a workbook with a new name.

 

[Ruddles]

After the save operation has completed, the new name of the workbook is in ThisWorkbook.Name or ThisWorkbook.FullName.

Is that what you mean?

If not, then seeing the code might give us more to go on.

 

[TimLundSE26]

That's what I want to capture, but this issue is when. Having saved the workbook with this new name, is there an event trigger that will let me do something with it automatically?

 

[Ruddles]

If you're performing the save operation via VBA, then that's the place to save the old and new names:-

strOldWorkbookName=ThisWorkbook.Name
ThisWorkbook.Save [COLOR=green]' or whatever[/COLOR]
strNewWorkbookName=ThisWorkbook.Name

Or is the save being triggered by the user clicking the 'save' button?

There's no Workbook_AfterSave but you could use another event to achieve the same effect. Do this in Workbook_BeforeSave:-

strOldWorkbookName=ThisWorkbook.Name
ThisWorkbook.Save [COLOR=green]' or whatever[/COLOR]
strNewWorkbookName=""

Then in Workbook_SheetChange or Workbook_SheetCalculate do:-

If strNewWorkbookName="" Then strNewWorkbookName=ThisWorkbook.Name

Does that help to move you forward at all?

 

[Ruddles]

Ooh, the other way I just thought of is to save the old file name, cancel the system save (Cancel=True), then use strNewWorkbookName=Application.GetSaveAsFilename to select a new filename. Then your new filename will be in strNewWorkbookName.

 

[TimLundSE26]

Thanks Ruddles - this does the business

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

Dim strWorkbookName As String
Dim strNewWorkbookName As String

If SaveAsUI Then

strWorkbookName = ThisWorkbook.Name
strNewWorkbookName = Application.GetSaveAsFilename

Cancel = True

RenameOwnMenu VBA.Right(strNewWorkbookName, InStr(StrReverse(strNewWorkbookName), "\") - 1)

Application.DisplayAlerts = False
ThisWorkbook.SaveAs strNewWorkbookName
Application.DisplayAlerts = True

End If


End Sub​

and

Public Sub RenameOwnMenu(strNewName As String)

CommandBars("Worksheet Menu Bar").Controls(ThisWorkbook.Name).Caption = strNewName

End Sub​

Where's the button to say problem fixed and your help was appreciated?

I really like this, not least because it's about the first time I've seen a use for those arguments in the built in event handlers.

 

[Ruddles]

Where's the button to say problem fixed and your help was appreciated?

In the blue bar immediately above the first message in the thread, there's a Rate Thread button.

I'm pleased you found an acceptable solution.