Increase number when saving

[Chamara Wickramasinghe]

I got an Excel template to input Invoice details and save. It has the Invoice Number in one cell and my requirement is to auto generate it when open the template. See the following directory.

e.g: C:\Directory -->

          Template.xls

          Invoice_01.xls

          Invoice_02.xls

When I open the Template.xls it should show 03 in Invoice Number cell and when I save the file it should be saved with a new file name Invoice_03.xls.

Is it possible to do this? Please help.

[#2]

Hey Chamara-

Hope your doing well.  Yes, this is possible with a little VBA Code:

You will need a workbook open event that triggers when the workbook is opened as well as a beforesave event that triggers before save as the naming convention denotes.  If your Invoice number is in cell A1 for example then you would want to do something like this:

This code needs to be put in the ThisWorkbook Code Module:

Private Sub Workbook_Open()
    Range("A1").Value = Range("A1").Value + 1
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Application.EnableEvents = False
 rtn = Application.Dialogs(xlDialogSaveAs).Show(arg1:="My FileName Invoice " & Range("A1").Value)
 Application.EnableEvents = True
 If Not rtn Then Cancel = True
End Sub