Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with my Unique Number Macro...Help?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with my Unique Number Macro...Help?
one last bump, I'm desperate here, this thing is so close
-- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Sort Unique Problem | Excel Discussion (Misc queries) | |||
How do i give unique number in set of duplicate number? | Excel Discussion (Misc queries) | |||
Unique Number | Excel Discussion (Misc queries) | |||
Finding unique names--then converting those names to unique number | Excel Discussion (Misc queries) | |||
Unique Number | Excel Worksheet Functions |