About Bob Phillips Expertise Excel formulae.
VBA.
Userforms, etc.etc.
Not really that hot on Excel charting, I use them, but feel that Excel lacks in this area and so are less useful than they should be.
Experience I am an IT professional for over 25 years, and have been using Excel and VBA for more than 10.
I provide training courses in Excel and VBA, and speak at Excel conferences.
I am a Microsoft MVP for Excel.
Education/Credentials I am educated to degree standard.
Expert: Bob Phillips Date: 8/13/2007 Subject: Auto number generation
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
