ExcelBanter - Problem with my Unique Number Macro...Help?

Here is what I have...
Dim InvoiceNumber As Variant

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("O3").ClearContents
End Sub

Private Sub Workbook_Open()
Create_New_Invoice_Number
End Sub

Public Sub Create_New_Invoice_Number()
On Error Resume Next
With Application
InvoiceNumber
Application.CommandBars("Dummy").Controls(1).Capti on
If IsEmpty(InvoiceNumber) Then
With .CommandBars.Add("Dummy")
.Controls.Add.Caption = 1000
.Enabled = False
End With
InvoiceNumber = 1
Else
.CommandBars("Dummy").Controls(1).Caption = Val(InvoiceNumber
+ 1
InvoiceNumber = Val(InvoiceNumber) + 1
End If
Range("O3") = InvoiceNumber
End With
End Sub

Here is what I had before, same idea just a different way to stor
things...

Private Sub Workbook_Open()
Dim InvNo&
'Do not assign new inv nbr if the original template has been opened
If UCase(Right(ThisWorkbook.Name, 3)) = "XLT" Then Exit Sub
'If O3 does not contain anything, enter the next inv nbr
If [O3] = "" Then
'Get next inv nbr
InvNo = GetSetting("XLInvoices", "Invoices", "CurrentNo", 1000) + 1
'Enter the inv nbr in A1
[O3] = InvNo
'Save the inv nbr
SaveSetting "XLInvoices", "Invoices", "CurrentNo", InvNo
'Put todays date in O4
[O4] = Date
End If
End Sub

Now with both of these codes though, I have a problem... this work
fine when I save the workbook first and then open a new template (thi
is part of a button feature) however, when I don't save, I run into th
problem that it will actually skip a number in terms of what I hav
saved. So I end up with missing invoices (they are only missing i
principle, because there is nothing in them technically) I though
about changing it to only adjust the invoice number before saving bu
then the current invoice being worked on would show the wrong invoic
number if printed from the current workbook and not the saved invoice.
This could get confusing after a while.

Thanks for any and all help. :

--
Message posted from http://www.ExcelForum.com