Sequential invoice numbers on multiple templates

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
I have 8 different invoices that I use on a daily bases. I have created templates for each that when saved, will send the data to a single database and save the invoice to a a specific folder using the invoice number as the file name. What I have haven't figured out yet is how to get an automactic sequential invoice number on the forms. Aslo if I use for example invoice 2222 on form 1, how can form 2 recognize the last number used in form 1 in order to use 2223 and so on skipping from form to form.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
L

Legacy 98055

Guest
Are your different invoice templates within the same workbook or 8 different workbooks?
Please discern the difference between a worksheet and a workbook. No offense intended if this is obvious to you, but so many people call a workbook a worksheet.
Thanks,
Tom
 

ROBINSYN

Board Regular
Joined
Aug 19, 2002
Messages
188
Thank you so much. You have just saved me years of headaches. I have been searching for 3 months to solve this. Thank you..........
 

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424

ADVERTISEMENT

TsTom
There are 8 different workbooks. One worksheet in each. However, I could put 8 worksheet in one workbook if that would simplify the problem. The data base is also just a single worksheet in it's own workbook
 
L

Legacy 98055

Guest
<pre>
Hi
There's no need to combine the workbooks into one.
If you want a customized example of the code below,
then please list the following.
The complete paths of all 8 of your workbooks and the
cell which holds the invoice number for each:


Ex C:My DocumentsTemplateOne.xls

Invoice number in cell D1


If you can edit the procedure yourself then there you have
it. This will add a button to the right-click "Cell"
commandbar popup called "Invoice Number"(The popup menu
which appears whenever you right click any given cell).
This button is added whenever any of these workbooks
are opened. All you will need to do to enter the next
logical invoice number is to right-click on the cell
containing the invoice number and then click the button
on the popup menu. The number may be placed in any cell
but will only be incremented if placed in the cell address
given in the routine.

This code will need to be pasted into the workbook module
of all of your invoice template workbooks. I have added
some descriptive variables which you can easily change the
assignments. To paste into the correct module, do the
following. From Excel's main window, right-click the
Excel icon located immediately left of the "File" menuitem,
choose "View Code", paste in as is.



'##################################################
CODE:

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("&Invoice Number...").Delete
Err.Number = 0
With Application.CommandBars("Cell").Controls.Add(msoControlButton, , , , True)
.Caption = "&Invoice Number..."
.OnAction = "ThisWorkbook.NextNum"
If Err.Number <> 0 Then _
Application.CommandBars("Cell").Controls("&Invoice Number...").Delete
.TooltipText = "Click to enter the next invoice number."
.Style = msoButtonCaption
End With
End Sub

Public Sub NextNum()
Dim TextFormula(1 To :cool: As String
'these formulas are temporarily added to your workbook to link to the
'other 7 closed or open templates to discern the
'next logical invoice number and need to be typed in correctly
'Example:
'TextFormula(1) = "='C:Documents and SettingsAdministratorDesktopExcelIncoices[TemplateOne.xls]Sheet1'!$A$1"

'The above formula is simply linking the open workbook to Cell A1 in the workbook located at this path.
'Fill in the 8 below with all 8 of your templates paths and references

TextFormula(1) = ""
TextFormula(2) = ""
TextFormula(3) = ""
TextFormula(4) = ""
TextFormula(5) = ""
TextFormula(6) = ""
TextFormula(7) = ""
TextFormula(8) = ""

With Sheets(1)
.Cells(65001, 256) = TextFormula(1)
.Cells(65002, 256) = TextFormula(1)
.Cells(65003, 256) = TextFormula(1)
.Cells(65004, 256) = TextFormula(1)
.Cells(65005, 256) = TextFormula(1)
.Cells(65006, 256) = TextFormula(1)
.Cells(65007, 256) = TextFormula(1)
.Cells(65008, 256) = TextFormula(1)
ActiveWorkbook.UpdateRemoteReferences = True
ActiveCell.Value = WorksheetFunction.Max(Range(.Cells(65001, 256), .Cells(65008, 256))) + 1
Range(.Cells(65001, 256), .Cells(65003, 256)).Clear
End With
End Sub

'##################################################
</pre>

Tom
 

Mr_Adams

Active Member
Joined
Oct 7, 2002
Messages
475

ADVERTISEMENT

You said if I would like a customized code to give you the follow

M:Ivoice Templates4 PAGE INVOICE.xlt
M:Ivoice TemplatesDHS RENT.xlt
M:Ivoice TemplatesGAS.xlt
M:Ivoice TemplatesGOODWILL RENT.xlt
M:Ivoice TemplatesHEALTH MAINTENANCE.xlt
M:Ivoice TemplatesPOSTAGE.xlt
M:Ivoice TemplatesPPWF BI-ANNUALLY.xlt
M:Ivoice TemplatesQTR DHS.xlt
M:Ivoice TemplatesQTR HEALTH.xlt
M:Ivoice TemplatesQTR PPWF.xlt
M:Ivoice TemplatesTRAVEL.xlt

Invoice number in cell V4

Just in case - Database is

M:InvoiceFinance Invoice Ledger.xls

Invoice numbers are in column A

Thank you Again Tom
 
L

Legacy 98055

Guest
Forget everything above. I missed the forest for all of the trees type thingy here.
Because of my blunder, I went out of my way to try and come up with a user-simple solution. All of your templates will need the following code placed in each of their workbook modules. The code adds two buttons to the right-click cell menu. One is to increment the invoice number, the other to save the invoice from the template without including the VBA procedures. The first time you run this, you will be prompted for the last invoice number entered. Thereafter, it should be automated. Let me know if you have any problems.
Unless the code is to be edited the following must remain unchanged:

This folder "M:Ivoice Templates" is where the small textfile is located which stores the invoice number. Directly editing this file will probably result in incorrect results. The given procedure will prompt you for the last invoice number entered.
Is "Ivoice" a typo or the name of your folder? The "n" is not there...
Also, the first sheet in each template is assumed to be the correct sheet. You mentioned that you only had one sheet per book. If any are added, this code will place the value in cell V4 on the first worksheet.

<pre>
Private Sub Workbook_Open()
CreateBar
End Sub

Private Sub Workbook_Activate()
CreateBar
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If MsgBox("To save this Invoice without including the VBA procedures " & Chr(13) & _
"please right-click any cell and choose ""Save Invoice""." & Chr(13) & _
"Choose cancel to save ""as is"".", vbSystemModal + vbOKCancel, "Save Options") <> vbCancel Then Cancel = True
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("Cell").Controls("&Invoice Number...").Delete
Application.CommandBars("Cell").Controls("Sa&ve Invoice...").Delete
Err.Number = 0
End Sub

Sub CreateBar()
On Error Resume Next
Application.CommandBars("Cell").Controls("&Invoice Number...").Delete
Application.CommandBars("Cell").Controls("Sa&ve Invoice...").Delete
Err.Number = 0
With Application.CommandBars("Cell").Controls.Add(msoControlButton, , , , True)
.Caption = "&Invoice Number..."
.OnAction = "ThisWorkbook.NextNum"
.TooltipText = "Click to enter the next invoice number."
.Style = msoButtonCaption
End With
With Application.CommandBars("Cell").Controls.Add(msoControlButton, , , , True)
.Caption = "Sa&ve Invoice..."
.OnAction = "ThisWorkbook.SaveInv"
.TooltipText = "Click to enter the next invoice number."
.Style = msoButtonCaption
End With
End Sub

Public Sub NextNum()
Dim of_1 As Integer, InvNum As Long
of_1 = FreeFile
Open "M:Ivoice TemplatesInvNum.txt" For Random As #of_1 Len = Len(InvNum)
Get #of_1, 1, InvNum
If InvNum = 0 Then InvNum = InputBox("Please enter the last invoive number used.")
InvNum = InvNum + 1
Put #of_1, 1, InvNum
Close #of_1
Range("V4").Value = InvNum
End Sub

Public Sub SaveInv()
Sheets(1).Copy
SaveAsF = Application.GetSaveAsFilename(, "Excel Spreadsheet (*.xls), *.xls")
If SaveAsF Then ActiveWorkbook.SaveAs SaveAsF
End Sub

</pre>

To get to your workbook module, right-click the Excel icon located directly left of the File menuitem, choose "View Code", paste in as is.

Tom
 

Mr_Adams

Active Member
Joined
Oct 7, 2002
Messages
475
oops, ivoice was a typo
I change that portion only and pasted as you said in all workbook templates.
The save will allow me to save "as is" by clicking cancel but if I click "ok" it closes the workbook without saving anything. When I right click any cell the options to "save invoice" does not apppear

There were no buttons added to the right-click cell menu. Is there something I might be doing wrong?
This message was edited by mr_adams on 2002-10-08 17:16
 

Forum statistics

Threads
1,143,923
Messages
5,721,559
Members
422,370
Latest member
A Nonomus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top