By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,584 Members | 1,750 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,584 IT Pros & Developers. It's quick & easy.

How Do I Auto Number Ms Excel Forms?

P: 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
Nov 12 '07 #1
Share this Question
Share on Google+
1 Reply


P: 17
Does anybody know????
Nov 16 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.