17
I operate a small business where i need to prepare Quotations, Invoices and Delivery forms. I am using Microsoft Office Excel. I would like to get some help to auto number these forms
If i open the forms for the first time it should start at say 001 and would increment by 1 ONLY when it is saved. So the second time i open the forms it should read 002 ONLY if it was saved, if not it should open up as 001.
I found this online and it actually increments when it is saved BUT the saved invoices do not open with the number it was saved as, but instead it open to the next available invoice number.
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
I have also heard that MS Access would solve all that, but i am a novice. I dont know how to use MS Access as yet. So please assist me with a formula that will work MS Excel.
need2know
