Save new workbook name in BeforeSave event?

[Roopher]

Hello all!

I have an issue with a command bar whose buttons shall work only when a certain workbook is active; this also when it has changed name due to an 'Save as...' operation. For that to work, a global variable needs to be updated every time when the workbook changes name.

The problem arises when the user saves the workbook under a new filename, which he is intended to be allowed (Save as...). With the BeforeSave event however I cannot catch the *new* filename into my comparison variable, but only the old one. I would need an event like 'AfterSave'.

Anyone with any idea on how to work this?

 

[Jonmo1]

why not use

ThisWorkbook.Name

Instead of hardcoding a bookname like
Workooks("MyBook.xls")

Hope that helps..

 

[Roopher]

Issue:
I am having a command bar with buttons. These buttons must work only for a certain workbook.
I try to store the name of that certain workbook in a global variable in order to compare the ThisWorkbook.Name with this variable in order to determine on button-click whether the buttons are allowed to work or not (for cases where another workbook is active).

Problem:
How to store a new workbook name into a global variable when the user does a 'Save As' operation, i.e. changing the workbook's name? The inital name gets saved fine on a 'WorkbookOpen' event.
---

Hi jonmo1, thanks for the suggestion!

Yes, I am trying using "MyVariable = ThisWorkbook.Name" within the 'BeforeSave' event, but this will stick the *old* workbook filename only into the variable when the user undertakes a 'Save As' operation (to save the (template-) file with a meaningful name). I would need to store the *new* filename that the user assigns. Hence, where is a 'AfterSave' event in Excel VBA...

Ironically, when I tried using the 'WorkbookDeactivate' event to catch the workbook name, then the workbook name I am switching *to* is stored by the "MyVariable = ThisWorkbook.Name" - but not the name I am coming *from*! .grrrrrr

A solution is, as I had done before once, to have CommandButtons on the spreadsheet itself, rather than having buttons on a command bar. However, that does not look too fancy...

 

[Jonmo1]

This might get you started in a direction...
Try this in the ThisWorkbook Module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim x As String
Application.EnableEvents = False
Cancel = True
x = Application.GetSaveAsFilename
If x = "False" Then Exit Sub
ThisWorkbook.SaveAs Filename:=x
x = ThisWorkbook.Name
MsgBox x
Application.EnableEvents = True
End Sub