normal save but with modified filename

[johnp]

I'm trying to force a file name when the default save button (or close) is triggered

I have a script that works when called as a macro, but when I drop it into the 'Thisworkbook' object it seems to run twice and then crash excel.

The script is below, but my question is more generic... what do I need to do if I want to force a file name change when generic save functions are called?

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

    Dim newFile As String, fName As String, fDate As String
       
' place date stamp in cell B1
    Range("B1").Value = Now
   
' build file_name string
    fName = Range("c1").Value
    fDate = Format$(Date, "dd-mmm-yyyy")
    newFile = fName & "_" & fDate
    
    ChDir   "D:\Documents and Settings\user\My Documents"
    ThisWorkbook.SaveAs Filename:=newFile
   
End Sub