Expert: Bob Phillips - 8/13/2007

Question
QUESTION: Hi,

I want to be able to create an invoice number that will automatically increase by one when I open the spreadsheet-but only if I have saved the invoice.

e.g

1.  Open file, number is 1000
2.  Save file and exit
3.  Next time, open file and number is 1001
4.  This time close file WITHOUT saving
5.  Next time, open file and number is 1001
6.  Save file and exit
7.  Next time I open file, number is 1002

I have tried the following macro, but the number increases regardless whether or not I have save the file.

Private Sub Workbook_Open()
  Range("F3").Value = GetSetting("Gill", "Gill", "Gill", 0) + 1
  SaveSetting "Gill", "Gill", "Gill", Range("F3").Value
End Sub

...help

Jamie

ANSWER: It's a bit more complex than that.

Try this

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mpFile As String
Dim mpValue As Long
Static mpReentry As Boolean

   If Not mpReentry Then
       mpReentry = True
       Application.EnableEvents = False
       Cancel = True
       mpValue = GetSetting("Gill", "Gill", "Gill", 0) + 1
       If MsgBox("Do you wish to save " & ThisWorkbook.Name & "?", vbYesNo, "Save File") = vbYes Then
           ThisWorkbook.Save
           SaveSetting "Gill", "Gill", "Gill", mpValue
       End If
       Application.EnableEvents = True
       ThisWorkbook.Close savechanges:=False
       mpReentry = False
   End If
End Sub

Private Sub Workbook_Open()
 ActiveSheet.Range("F3").Value = GetSetting("Gill", "Gill", "Gill", 0)
End Sub

---------- FOLLOW-UP ----------

QUESTION: Hi,

Thanks - this is exactly what I want.

I have one more question however - As I have been playing, the number I have been incrementing is now high; how do I reset it back to zero so that it will increment from there on?

Regards

Answer
Jamie,

just track it down in the registry and reset it.

Or put a limit on it

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mpFile As String
Dim mpValue As Long
Static mpReentry As Boolean

  If Not mpReentry Then
      mpReentry = True
      Application.EnableEvents = False
      Cancel = True
      mpValue = GetSetting("Gill", "Gill", "Gill", 0) + 1
      If MsgBox("Do you wish to save " & ThisWorkbook.Name & "?", vbYesNo, "Save File") = vbYes Then
          If mpValue > 1000 then mpValue = 1 '<<<< change to suit
          ThisWorkbook.Save
          SaveSetting "Gill", "Gill", "Gill", mpValue
      End If
      Application.EnableEvents = True
      ThisWorkbook.Close savechanges:=False
      mpReentry = False
  End If
End Sub

Private Sub Workbook_Open()
ActiveSheet.Range("F3").Value = GetSetting("Gill", "Gill", "Gill", 0)
End Sub