How do I set up incremental numbering of Invoices in Excel?

[TFT]

I am trying to create a procedure within Excel which will allow me to
automatically bring up a sequentially numbered invoice for my small business.
Designing invoices is not a problem but trying to automate them so that they
appear with a follow up number is eluding me.

 

[JE McGimpsey]

A couple of ways:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

 

[Mike Fogleman]

Say your invoice number is in cell H1, put this code in the ThisWorkbook
module:

Private Sub Workbook_Open()
Range("H1").Value = Range("H1").Value + 1
Range("H1").Copy
Range("H1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Change "H1" to whatever cell you want to use.

Mike F

 

[TFT]

I shall endeavour to follow the instructions you have provided in due course
but at first sight I guess it is going to require more effort on my part to
learn something about macros. Many thanks for your assistance.

 

[TFT]

Please could you clarify. I am not familiar with macros at this stage
although I'm working on it. Am I to assume that the code you have presented
is inserted in a newly created macro?

 

[Mike Fogleman]

With your workbook open, right-click the sheet tab and from the menu that
pops up select View Code. This will bring up a second window called the
VBE - Visual Basic Editor. All macros go here. In the left window is a
directory tree of all open (Projects) Excel workbooks and add-ins. One of
them will be VBAProject (Your workbook name). Under that is a folder named
Microsoft Excel Objects which has a list of all the worksheets and at the
bottom of that list an object called ThisWorkbook with an Excel icon on it.
Double-click that icon to display its code module in the right window pane.
Copy/Paste my code into that window. Carefully edit my code everywhere it
has H1 and replace it with the cell address where you want the incrementing
numbers. Save your workbook. Now every time you close and re-open the
workbook, the number in the cell you chose will increase by 1.
You can custom format the cell with "000" that will display leading 0's for
numbers less than 100 eg. (009, 099, etc)
Mike F

 

[Reetesh B. Chhatpar]

Hi,

Dim WSHShell, RegKey, rkeyWord, Result
Set WSHShell = CreateObject("WScript.Shell")
RegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\MsWordNum\"

rkeyWord = WSHShell.RegRead(RegKey & "Word")
Dim xx As Integer
xx = Val(rkeyWord) + 1
Dim yy As String
yy = Right("00000" + Trim(Str(xx)), 5)
WSHShell.regwrite RegKey & "Word", yy

' here the code is to print in header the registry value
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.WholeStory
Selection.Delete Unit:=wdCharacter, Count:=1

Selection.TypeText Text:=yy + " C_ME_C "

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"DATE \@ ""d/M/yyyy"" ", PreserveFormatting:=True

If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader


'to close the header / footer view
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

I hope this helps !

Reetesh B. Chhatpar
ShawMan Software Enterprises
www.shawmansoftware.com

Diamond is just another piece of coal that did well under pressure.

 

[TFT]

At last, I have a result! This is something I have been trying to get sorted
for some considerable time now. Having tested this suggestion I am now
satisfied it answers my quest ... thank you so much!
TFT

 

[TFT]

Oops .... I was a bit hasty with my follow up to your suggestion Mike, I have
come up against a snag with this procedure.
Until now I have been using M/S Access 2000 to run my invoicing system and
in this it was possible to keep a record of all my invoices in a database,
thus giving me access when I needed to reference them.
How can this be done in Excel? It would appear that each time I open the
workbook, although a new number is generated there is no going back to the
last report.

 

[Office User]

I don't know where my brain lapse is happening but I'm struggling trying to
figure out this code. I'm utilizing the one to have text file store the
numbers.

My InvoiceNumber.txt file is located on c:\. The file is blank except for
the number 100. Here's part of your code with my file location and name.

Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "c:\"
Const sDEFAULT_FNAME As String = "InvoiceNumber.txt"
Dim nFileNumber As Long

When I open the template file, I don't get any error message but it doesn't
update the cell with a number. Currently cell L4 is blank.

Public Sub Workbook_Open()
ThisWorkbook.Sheets(1).Range("L4").Value = NextSeqNumber
End Sub

Both the Function and Sub are saved in This.Workbook.

I'm sure it's an obvious oversight on my part but any help would be greatly
appreciated.

Thanks,
Marcia

 

[Zack Barresse]

Hello,

This may help ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=351

 

[Office User]

Thanks Zack. It did help and I've bookmarked the site for future reference
as I learn VBA. I've only muddled my way thru figuring out some of the VBA
stuff so far but will be taking an actual VBA class in another month.

Thanks again,
Marcia