CODEWITHSUNDEEP
excelvba
Nathaniel_613
Nathaniel_613

Reputation: 179

Excel 2003 template that saves the filename based on a cell value

I need to make an Excel 2003 template to be used a customer order entry form, that when the user saves the file, it will automatically save with the filename with the value of cell B2 (customer ID) as the prefix and the current date as the suffix e.g. “2001XC_5-24-12”. I don’t know VBA. Thank you very much in advance, Nathaniel

Upvotes: 0

Views: 1286

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

You can use a BeforeSave event handler to do this.

Put this code in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    ThisWorkbook.SaveAs [B2] & "_" & Format(Date, "m-d-yy")
    Cancel = True
    Application.EnableEvents = True
End Sub

Note:

  • You need to account for which sheet you want cell B2 from, so use something like:

    ThisWorkbook.SaveAs ThisWorkbook.Worksheets("Sheet Name Here").Cells(2,2) ...

  • When creating file names with dates, I reccomend you use formay yyyymmdd so they will sort chronologically

Upvotes: 4

Related Questions