Excel/Auto number generation
Expert: Bob Phillips - 8/13/2007
QuestionQUESTION: 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
AnswerJamie,
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