469,159 Members | 1,504 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

How Do I Auto Number Ms Excel Forms?

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
1 2515
Does anybody know????
Nov 16 '07 #2

Post your reply

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

Similar topics

9 posts views Thread by Dayne | last post: by
1 post views Thread by Brian Conklin | last post: by
22 posts views Thread by Howard Kaikow | last post: by
reply views Thread by cappa | last post: by
2 posts views Thread by jeswin12 | last post: by
1 post views Thread by turtle | last post: by
13 posts views Thread by S.Dickson | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.