Referencing cell value for workbook name

[benntw]

I have a userform and I use (vbModeless). I have the code Windows("my workbook name here").activate. This allows me to have any other excel open and my code to still go to my workbook with the userform. On sheet for cell A1 I put the formula =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1) . This gives me my current workbook name. If the workbook gets renamed I want to be able to refer to cell A1 to show my current workbook name in the Windows Activate. I have tried to figure out the code with no luck. Does anyone have a way to still use the Windows Activate my workbook referring to A1 ? Thank you

 

[JLGWhiz]

Only the person causing the name change would be able to provide that bit of information. But you could use a Workbook_BeforeSave() event macro to ask the user to fill in the name.
Goes in the ThisWorkbook code module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ans = MsgBox("Did you enter the new workbook name in cell A1?  Click 'Yes' if name was not changed.", vbYesNo, "New File Name"
    If ans = vbNo Then
        Cancel = True
    End If
Emd Sib

 

[benntw]

Thank you. I'll save this for another project. Appreciate the input.

 

[JLGWhiz]

You're welcome,
regards, JLG