Auto Number in Invoice
 

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

OH I FORGOT TO SAY MY INVOICE NUMBER IS IN CELL `E5` - THIS IS WHERE I WANT
IT TO CHANGE EACH TIME

"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

Sounds like your problem is with the two Const statements at the beginning of
the function?
-----
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
-----

The first one should be set to the path to where the text file containing
the sequence of numbers is kept. The "<your path here" part should actually
look something like this:
"C:\Documents and Settings\PammiJ\My Documents\"

For the second line, yes you need to create a file somewhere (in the folder
pointed to by the first line) and all it needs to contain initially is the
first invoice number you want to use, minus 1.

Create the file using Notepad: open Notepad, start a new file, just type a
number into it (assume you want invoices to start at 1, type 0 into it).
Then use Save As to give it a name like the one he suggested or you could
give it some other name like PammiJs_InvoiceNumbers.txt and save it in the
path you have decided on. Close Notepad.

Now your first two lines in that function should look something like this:
Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My
Documents\"
Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt"

I think the McGimpsey page uses cell B2 as the one where the invoice number
is to appear, just change "B2" in his sample code to "E5" and it should work.

Only thing that might keep it from working is your Macro Security - make
sure macros are permitted to run - best setting that's a balance of security
and convenience is MEDIUM where you are prompted when the workbook is opened
whether or not to let macro code execute. That will keep you from being
unpleasantly surprised if someone sends you an Excel workbook with code in it
that you weren't expecting.

One other thing that can also keep macros from running these days is your
anti-virus application. Some of those have a setting in them that prevents
VBA code from running, and if yours is one of those (I use Kaspersky Business
Optimal and it has such a setting) then you'll need to set it to allow you to
run the code.


"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

Hi and many thanks for your quick reply.
I have done that and saved my invoice as a template but when i open there
is no number in my e5 cell.
Here is the code i put in
Have i done something wrong - my macro security is med and firewall ect is
not preventing macros from running.

Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "<C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"
Const sDEFAULT_FNAME As String = "inv no.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
With ThisWorkbook.Sheets(1)
.Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt",
849)
End With
End Sub

"JLatham" wrote:

Sounds like your problem is with the two Const statements at the beginning of
the function?
-----
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
-----

The first one should be set to the path to where the text file containing
the sequence of numbers is kept. The "<your path here" part should actually
look something like this:
"C:\Documents and Settings\PammiJ\My Documents\"

For the second line, yes you need to create a file somewhere (in the folder
pointed to by the first line) and all it needs to contain initially is the
first invoice number you want to use, minus 1.

Create the file using Notepad: open Notepad, start a new file, just type a
number into it (assume you want invoices to start at 1, type 0 into it).
Then use Save As to give it a name like the one he suggested or you could
give it some other name like PammiJs_InvoiceNumbers.txt and save it in the
path you have decided on. Close Notepad.

Now your first two lines in that function should look something like this:
Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My
Documents\"
Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt"

I think the McGimpsey page uses cell B2 as the one where the invoice number
is to appear, just change "B2" in his sample code to "E5" and it should work.

Only thing that might keep it from working is your Macro Security - make
sure macros are permitted to run - best setting that's a balance of security
and convenience is MEDIUM where you are prompted when the workbook is opened
whether or not to let macro code execute. That will keep you from being
unpleasantly surprised if someone sends you an Excel workbook with code in it
that you weren't expecting.

One other thing that can also keep macros from running these days is your
anti-virus application. Some of those have a setting in them that prevents
VBA code from running, and if yours is one of those (I use Kaspersky Business
Optimal and it has such a setting) then you'll need to set it to allow you to
run the code.


"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

You are pushing my capability but... If you save this as a template (*.xlt)
when you open a <File <New copy (*+1,2,3.xls) it is an exact copy of your
existing template. I think what you need is a look-up cell somewhere else
=E5+1 probably as a "Workbook_Open()" event.
HTH
Lou

"Pammi J" wrote:

Hi and many thanks for your quick reply.
I have done that and saved my invoice as a template but when i open there
is no number in my e5 cell.
Here is the code i put in
Have i done something wrong - my macro security is med and firewall ect is
not preventing macros from running.

Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "<C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"
Const sDEFAULT_FNAME As String = "inv no.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
With ThisWorkbook.Sheets(1)
.Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt",
849)
End With
End Sub

"JLatham" wrote:

Sounds like your problem is with the two Const statements at the beginning of
the function?
-----
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
-----

The first one should be set to the path to where the text file containing
the sequence of numbers is kept. The "<your path here" part should actually
look something like this:
"C:\Documents and Settings\PammiJ\My Documents\"

For the second line, yes you need to create a file somewhere (in the folder
pointed to by the first line) and all it needs to contain initially is the
first invoice number you want to use, minus 1.

Create the file using Notepad: open Notepad, start a new file, just type a
number into it (assume you want invoices to start at 1, type 0 into it).
Then use Save As to give it a name like the one he suggested or you could
give it some other name like PammiJs_InvoiceNumbers.txt and save it in the
path you have decided on. Close Notepad.

Now your first two lines in that function should look something like this:
Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My
Documents\"
Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt"

I think the McGimpsey page uses cell B2 as the one where the invoice number
is to appear, just change "B2" in his sample code to "E5" and it should work.

Only thing that might keep it from working is your Macro Security - make
sure macros are permitted to run - best setting that's a balance of security
and convenience is MEDIUM where you are prompted when the workbook is opened
whether or not to let macro code execute. That will keep you from being
unpleasantly surprised if someone sends you an Excel workbook with code in it
that you weren't expecting.

One other thing that can also keep macros from running these days is your
anti-virus application. Some of those have a setting in them that prevents
VBA code from running, and if yours is one of those (I use Kaspersky Business
Optimal and it has such a setting) then you'll need to set it to allow you to
run the code.


"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

I'll look at this more this evening - but I believe that Rookie 1st class is
on the right track. The original code was designed to be driven from the
Workbook_Open event.

My question on your routine is What is in E5 when you make the call from
within your SetUpNewClient() routine? That is expected to be the name of the
file with the sequence numbers in it (Inv no.txt). Oh, and I recommend you
do away with the space in that file name - they just cause issues when
programming with filenames or paths that contain spaces. Try renaming the
file (and changing the code) to something line InvNo.txt or Inv_no.txt. Not
because it shouldn't work, but because it's easier to work with in general.

"Pammi J" wrote:

Hi and many thanks for your quick reply.
I have done that and saved my invoice as a template but when i open there
is no number in my e5 cell.
Here is the code i put in
Have i done something wrong - my macro security is med and firewall ect is
not preventing macros from running.

Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "<C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"
Const sDEFAULT_FNAME As String = "inv no.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
With ThisWorkbook.Sheets(1)
.Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt",
849)
End With
End Sub

"JLatham" wrote:

Sounds like your problem is with the two Const statements at the beginning of
the function?
-----
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
-----

The first one should be set to the path to where the text file containing
the sequence of numbers is kept. The "<your path here" part should actually
look something like this:
"C:\Documents and Settings\PammiJ\My Documents\"

For the second line, yes you need to create a file somewhere (in the folder
pointed to by the first line) and all it needs to contain initially is the
first invoice number you want to use, minus 1.

Create the file using Notepad: open Notepad, start a new file, just type a
number into it (assume you want invoices to start at 1, type 0 into it).
Then use Save As to give it a name like the one he suggested or you could
give it some other name like PammiJs_InvoiceNumbers.txt and save it in the
path you have decided on. Close Notepad.

Now your first two lines in that function should look something like this:
Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My
Documents\"
Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt"

I think the McGimpsey page uses cell B2 as the one where the invoice number
is to appear, just change "B2" in his sample code to "E5" and it should work.

Only thing that might keep it from working is your Macro Security - make
sure macros are permitted to run - best setting that's a balance of security
and convenience is MEDIUM where you are prompted when the workbook is opened
whether or not to let macro code execute. That will keep you from being
unpleasantly surprised if someone sends you an Excel workbook with code in it
that you weren't expecting.

One other thing that can also keep macros from running these days is your
anti-virus application. Some of those have a setting in them that prevents
VBA code from running, and if yours is one of those (I use Kaspersky Business
Optimal and it has such a setting) then you'll need to set it to allow you to
run the code.


"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

Aha!
In the definition for Const sDEFAULT_PATH, get rid of the < and at the
beginning and end of the string - they should not be in there. It should
look like this:

Const sDEFAULT_PATH As String = "C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"

Plus, you remember that your own SetUpNewClient routine has to be run from
Tools | Macro | Macros

I messed with this some, and tried to 'read your mind' a little, I may have
failed at that. I'm assuming that a customer/client's name is going to be in
E5 or else E5 may be totally empty. This code takes both cases into account.

I moved the Const declarations out of the sub into the body of the module
and made them Public Constants so that both routines can reference them to
get their values.

I didn't change too much in the Function but I beat up on the Sub a bit to
get it to decide whether to use whatever is in E5 as the basis for a
filename, to see if it exists already (existing customer) or if this is a new
customer and starting from ground zero. Hopefully the comments will explain
it all well enough.

Here goes - replaces all that you currently have (the board will rpobably
wreak havoc on some of the longer lines of code, watch out - like your path
should all be on a single line, the rest I think I broke properly) Just in
case, I uploaded a working version that you can get he
http://www.jlathamsite.com/uploads/for_pammij.xls


Public Const sDEFAULT_PATH As String _
= "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair
Service\Invoices\"
Public Const sDEFAULT_FNAME = "inv no.txt"

Public Function NextSeqNumber(Optional sFileName As String, _
Optional nSeqNumber As Long = -1) As Long
Dim nFileNumber As Long

nFileNumber = FreeFile
If IsMissing(sFileName) Or sFileName = "" Then
sFileName = sDEFAULT_FNAME
End If
If InStr(sFileName, Application.PathSeparator) = 0 Then
sFileName = sDEFAULT_PATH & Application.PathSeparator _
& sFileName
End If
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
Dim ClientInvoiceNumberFile As String
Dim SequenceNumberMinus1 As Long
Dim sFileName As String
Dim nFileNumber As Integer

With ThisWorkbook.Sheets(1)
If IsEmpty(.Range("E5")) Then
'will force read from default .txt file
ClientInvoiceNumberFile = sDEFAULT_FNAME
Else
'will read from specified file
ClientInvoiceNumberFile = .Range("E5") & ".txt"
End If
sFileName = sDEFAULT_PATH & Application.PathSeparator _
& ClientInvoiceNumberFile
If Dir$(sFileName) = "" Then
'file does not exist, New Customer!
'create unique file for them
nFileNumber = FreeFile()
Open sFileName For Output As nFileNumber
Print #nFileNumber, 849 ' to start at 850 for new customers
Close #nFileNumber
Else
'the file exists and so we
'presume it is an existing customer with
'existing invoice number in it
'and we DO NOTHING
End If
'now we have a file that exists and
'it has some number in it
'call the function to do the work
.Range("B2").Value = NextSeqNumber(sFileName, -1)
End With
End Sub



"Pammi J" wrote:

Hi and many thanks for your quick reply.
I have done that and saved my invoice as a template but when i open there
is no number in my e5 cell.
Here is the code i put in
Have i done something wrong - my macro security is med and firewall ect is
not preventing macros from running.

Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "<C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"
Const sDEFAULT_FNAME As String = "inv no.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
With ThisWorkbook.Sheets(1)
.Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt",
849)
End With
End Sub

"JLatham" wrote:

Sounds like your problem is with the two Const statements at the beginning of
the function?
-----
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
-----

The first one should be set to the path to where the text file containing
the sequence of numbers is kept. The "<your path here" part should actually
look something like this:
"C:\Documents and Settings\PammiJ\My Documents\"

For the second line, yes you need to create a file somewhere (in the folder
pointed to by the first line) and all it needs to contain initially is the
first invoice number you want to use, minus 1.

Create the file using Notepad: open Notepad, start a new file, just type a
number into it (assume you want invoices to start at 1, type 0 into it).
Then use Save As to give it a name like the one he suggested or you could
give it some other name like PammiJs_InvoiceNumbers.txt and save it in the
path you have decided on. Close Notepad.

Now your first two lines in that function should look something like this:
Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My
Documents\"
Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt"

I think the McGimpsey page uses cell B2 as the one where the invoice number
is to appear, just change "B2" in his sample code to "E5" and it should work.

Only thing that might keep it from working is your Macro Security - make
sure macros are permitted to run - best setting that's a balance of security
and convenience is MEDIUM where you are prompted when the workbook is opened
whether or not to let macro code execute. That will keep you from being
unpleasantly surprised if someone sends you an Excel workbook with code in it
that you weren't expecting.

One other thing that can also keep macros from running these days is your
anti-virus application. Some of those have a setting in them that prevents
VBA code from running, and if yours is one of those (I use Kaspersky Business
Optimal and it has such a setting) then you'll need to set it to allow you to
run the code.


"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

isnt there an easier way to add a formula into the cell where the invoice
number is shown on the template to create auto numbering ? Real confused
here, looks like programing to me. Thought Excel was already complete with
automating codes for this sort of problem ?
--
lizard1socal


"JLatham" wrote:

Aha!
In the definition for Const sDEFAULT_PATH, get rid of the < and at the
beginning and end of the string - they should not be in there. It should
look like this:

Const sDEFAULT_PATH As String = "C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"

Plus, you remember that your own SetUpNewClient routine has to be run from
Tools | Macro | Macros

I messed with this some, and tried to 'read your mind' a little, I may have
failed at that. I'm assuming that a customer/client's name is going to be in
E5 or else E5 may be totally empty. This code takes both cases into account.

I moved the Const declarations out of the sub into the body of the module
and made them Public Constants so that both routines can reference them to
get their values.

I didn't change too much in the Function but I beat up on the Sub a bit to
get it to decide whether to use whatever is in E5 as the basis for a
filename, to see if it exists already (existing customer) or if this is a new
customer and starting from ground zero. Hopefully the comments will explain
it all well enough.

Here goes - replaces all that you currently have (the board will rpobably
wreak havoc on some of the longer lines of code, watch out - like your path
should all be on a single line, the rest I think I broke properly) Just in
case, I uploaded a working version that you can get he
http://www.jlathamsite.com/uploads/for_pammij.xls


Public Const sDEFAULT_PATH As String _
= "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair
Service\Invoices\"
Public Const sDEFAULT_FNAME = "inv no.txt"

Public Function NextSeqNumber(Optional sFileName As String, _
Optional nSeqNumber As Long = -1) As Long
Dim nFileNumber As Long

nFileNumber = FreeFile
If IsMissing(sFileName) Or sFileName = "" Then
sFileName = sDEFAULT_FNAME
End If
If InStr(sFileName, Application.PathSeparator) = 0 Then
sFileName = sDEFAULT_PATH & Application.PathSeparator _
& sFileName
End If
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
Dim ClientInvoiceNumberFile As String
Dim SequenceNumberMinus1 As Long
Dim sFileName As String
Dim nFileNumber As Integer

With ThisWorkbook.Sheets(1)
If IsEmpty(.Range("E5")) Then
'will force read from default .txt file
ClientInvoiceNumberFile = sDEFAULT_FNAME
Else
'will read from specified file
ClientInvoiceNumberFile = .Range("E5") & ".txt"
End If
sFileName = sDEFAULT_PATH & Application.PathSeparator _
& ClientInvoiceNumberFile
If Dir$(sFileName) = "" Then
'file does not exist, New Customer!
'create unique file for them
nFileNumber = FreeFile()
Open sFileName For Output As nFileNumber
Print #nFileNumber, 849 ' to start at 850 for new customers
Close #nFileNumber
Else
'the file exists and so we
'presume it is an existing customer with
'existing invoice number in it
'and we DO NOTHING
End If
'now we have a file that exists and
'it has some number in it
'call the function to do the work
.Range("B2").Value = NextSeqNumber(sFileName, -1)
End With
End Sub



"Pammi J" wrote:

Hi and many thanks for your quick reply.
I have done that and saved my invoice as a template but when i open there
is no number in my e5 cell.
Here is the code i put in
Have i done something wrong - my macro security is med and firewall ect is
not preventing macros from running.

Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "<C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"
Const sDEFAULT_FNAME As String = "inv no.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
With ThisWorkbook.Sheets(1)
.Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt",
849)
End With
End Sub

"JLatham" wrote:

Sounds like your problem is with the two Const statements at the beginning of
the function?
-----
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
-----

The first one should be set to the path to where the text file containing
the sequence of numbers is kept. The "<your path here" part should actually
look something like this:
"C:\Documents and Settings\PammiJ\My Documents\"

For the second line, yes you need to create a file somewhere (in the folder
pointed to by the first line) and all it needs to contain initially is the
first invoice number you want to use, minus 1.

Create the file using Notepad: open Notepad, start a new file, just type a
number into it (assume you want invoices to start at 1, type 0 into it).
Then use Save As to give it a name like the one he suggested or you could
give it some other name like PammiJs_InvoiceNumbers.txt and save it in the
path you have decided on. Close Notepad.

Now your first two lines in that function should look something like this:
Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My
Documents\"
Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt"

I think the McGimpsey page uses cell B2 as the one where the invoice number
is to appear, just change "B2" in his sample code to "E5" and it should work.

Only thing that might keep it from working is your Macro Security - make
sure macros are permitted to run - best setting that's a balance of security
and convenience is MEDIUM where you are prompted when the workbook is opened
whether or not to let macro code execute. That will keep you from being
unpleasantly surprised if someone sends you an Excel workbook with code in it
that you weren't expecting.

One other thing that can also keep macros from running these days is your
anti-virus application. Some of those have a setting in them that prevents
VBA code from running, and if yours is one of those (I use Kaspersky Business
Optimal and it has such a setting) then you'll need to set it to allow you to
run the code.


"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

--
lizard1socal


"lizard1socal" wrote:

isnt there an easy way to click a button on the toolbar to add a formula into the cell where the invoice
number is shown on the template to create auto numbering ? Real confused
here, looks like programing to me. Thought Excel was already complete with
automating codes for this sort of problem ? Is it really that hard to enable the numbering function as to have to write a program ?
--
lizard1socal

Auto Number in Invoice
 

You may be thinking of Access's AutoNumber type which is an auto-numbering
feature that can be used in tables for a variety of purposes, the most
typical being that of providing a unique key field value for a table.

To the best of my knowledge there is no built in function to simply
auto-increment a value in Excel based on some action like the opening of a
workbook. Excel contains many incredibly powerful functions; I've an
internet acquaintance that has kind of put me to shame several times in
coming up with a one-line cell formula (albeit somewhat complex usually) to
do what I took many lines of VBA code to achieve. But in some instances
there are simply things that cannot be done with a worksheet function. I
believe this is one of them.

Now there is a simple formula such as
=A1+1
which would add one to the value in A1 ... but A1 has no real way of getting
automatically updated, so the value in the cell with the formula in it never
really changes either.

There is no command like =ME+1 which is my fictitious way of creating a
formula that would increment itself.

I've seen many ways to try to set up an auto-incrementing scheme, and all
that I remember are coded at some level or another. Besides the use of a
text file here to hold the last used invoice number, one way I've seen is to
use Excel's Define Names function to store the value in a Name within the
workbook itself. That's a neat little way to do it also, but involves code
also. Other schemes involve using a hidden cell somewhere to hold the last
invoice number used and then take that value and increment it when the
workbook is opened or when the user chooses to run a macro to assign the next
invoice number to a cell. Again, some code is required.

There are basically two issues to be dealt with in trying to get an auto
incrementing number that is persistent between uses of the workbook:
#1 - determining where to store the value that is the basis for the next
number in sequence, and
#2 - determining/controlling exactly when that number is incremented.

It would appear that it takes some VBA code to provide this functionality.


"lizard1socal" wrote:

isnt there an easier way to add a formula into the cell where the invoice
number is shown on the template to create auto numbering ? Real confused
here, looks like programing to me. Thought Excel was already complete with
automating codes for this sort of problem ?
--
lizard1socal


"JLatham" wrote:

Aha!
In the definition for Const sDEFAULT_PATH, get rid of the < and at the
beginning and end of the string - they should not be in there. It should
look like this:

Const sDEFAULT_PATH As String = "C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"

Plus, you remember that your own SetUpNewClient routine has to be run from
Tools | Macro | Macros

I messed with this some, and tried to 'read your mind' a little, I may have
failed at that. I'm assuming that a customer/client's name is going to be in
E5 or else E5 may be totally empty. This code takes both cases into account.

I moved the Const declarations out of the sub into the body of the module
and made them Public Constants so that both routines can reference them to
get their values.

I didn't change too much in the Function but I beat up on the Sub a bit to
get it to decide whether to use whatever is in E5 as the basis for a
filename, to see if it exists already (existing customer) or if this is a new
customer and starting from ground zero. Hopefully the comments will explain
it all well enough.

Here goes - replaces all that you currently have (the board will rpobably
wreak havoc on some of the longer lines of code, watch out - like your path
should all be on a single line, the rest I think I broke properly) Just in
case, I uploaded a working version that you can get he
http://www.jlathamsite.com/uploads/for_pammij.xls


Public Const sDEFAULT_PATH As String _
= "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair
Service\Invoices\"
Public Const sDEFAULT_FNAME = "inv no.txt"

Public Function NextSeqNumber(Optional sFileName As String, _
Optional nSeqNumber As Long = -1) As Long
Dim nFileNumber As Long

nFileNumber = FreeFile
If IsMissing(sFileName) Or sFileName = "" Then
sFileName = sDEFAULT_FNAME
End If
If InStr(sFileName, Application.PathSeparator) = 0 Then
sFileName = sDEFAULT_PATH & Application.PathSeparator _
& sFileName
End If
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
Dim ClientInvoiceNumberFile As String
Dim SequenceNumberMinus1 As Long
Dim sFileName As String
Dim nFileNumber As Integer

With ThisWorkbook.Sheets(1)
If IsEmpty(.Range("E5")) Then
'will force read from default .txt file
ClientInvoiceNumberFile = sDEFAULT_FNAME
Else
'will read from specified file
ClientInvoiceNumberFile = .Range("E5") & ".txt"
End If
sFileName = sDEFAULT_PATH & Application.PathSeparator _
& ClientInvoiceNumberFile
If Dir$(sFileName) = "" Then
'file does not exist, New Customer!
'create unique file for them
nFileNumber = FreeFile()
Open sFileName For Output As nFileNumber
Print #nFileNumber, 849 ' to start at 850 for new customers
Close #nFileNumber
Else
'the file exists and so we
'presume it is an existing customer with
'existing invoice number in it
'and we DO NOTHING
End If
'now we have a file that exists and
'it has some number in it
'call the function to do the work
.Range("B2").Value = NextSeqNumber(sFileName, -1)
End With
End Sub



"Pammi J" wrote:

Hi and many thanks for your quick reply.
I have done that and saved my invoice as a template but when i open there
is no number in my e5 cell.
Here is the code i put in
Have i done something wrong - my macro security is med and firewall ect is
not preventing macros from running.

Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "<C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"
Const sDEFAULT_FNAME As String = "inv no.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
With ThisWorkbook.Sheets(1)
.Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt",
849)
End With
End Sub

"JLatham" wrote:

Sounds like your problem is with the two Const statements at the beginning of
the function?
-----
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
-----

The first one should be set to the path to where the text file containing
the sequence of numbers is kept. The "<your path here" part should actually
look something like this:
"C:\Documents and Settings\PammiJ\My Documents\"

For the second line, yes you need to create a file somewhere (in the folder
pointed to by the first line) and all it needs to contain initially is the
first invoice number you want to use, minus 1.

Create the file using Notepad: open Notepad, start a new file, just type a
number into it (assume you want invoices to start at 1, type 0 into it).
Then use Save As to give it a name like the one he suggested or you could
give it some other name like PammiJs_InvoiceNumbers.txt and save it in the
path you have decided on. Close Notepad.

Now your first two lines in that function should look something like this:
Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My
Documents\"
Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt"

I think the McGimpsey page uses cell B2 as the one where the invoice number
is to appear, just change "B2" in his sample code to "E5" and it should work.

Only thing that might keep it from working is your Macro Security - make
sure macros are permitted to run - best setting that's a balance of security
and convenience is MEDIUM where you are prompted when the workbook is opened
whether or not to let macro code execute. That will keep you from being
unpleasantly surprised if someone sends you an Excel workbook with code in it
that you weren't expecting.

One other thing that can also keep macros from running these days is your
anti-virus application. Some of those have a setting in them that prevents
VBA code from running, and if yours is one of those (I use Kaspersky Business
Optimal and it has such a setting) then you'll need to set it to allow you to
run the code.


"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

Hi
Thanks for replying everyone
E5 cell contains (or will hopefully contain) the auto invoice number. My
customer details are hopefully going to come from access database (but i
havent started that connection yet). So basically it is just one cell i need
the code for.
I have tried jiggling that code but again the template is opening up with
cell E5 blank.
I will try all different things to see if i can get it to work somehow - it
will not get the better of me.
It would be good if this autonumber thing was included within excel -
similar to the auto date function. As it seems a lot of people have the same
issue.


"JLatham" wrote:

You may be thinking of Access's AutoNumber type which is an auto-numbering
feature that can be used in tables for a variety of purposes, the most
typical being that of providing a unique key field value for a table.

To the best of my knowledge there is no built in function to simply
auto-increment a value in Excel based on some action like the opening of a
workbook. Excel contains many incredibly powerful functions; I've an
internet acquaintance that has kind of put me to shame several times in
coming up with a one-line cell formula (albeit somewhat complex usually) to
do what I took many lines of VBA code to achieve. But in some instances
there are simply things that cannot be done with a worksheet function. I
believe this is one of them.

Now there is a simple formula such as
=A1+1
which would add one to the value in A1 ... but A1 has no real way of getting
automatically updated, so the value in the cell with the formula in it never
really changes either.

There is no command like =ME+1 which is my fictitious way of creating a
formula that would increment itself.

I've seen many ways to try to set up an auto-incrementing scheme, and all
that I remember are coded at some level or another. Besides the use of a
text file here to hold the last used invoice number, one way I've seen is to
use Excel's Define Names function to store the value in a Name within the
workbook itself. That's a neat little way to do it also, but involves code
also. Other schemes involve using a hidden cell somewhere to hold the last
invoice number used and then take that value and increment it when the
workbook is opened or when the user chooses to run a macro to assign the next
invoice number to a cell. Again, some code is required.

There are basically two issues to be dealt with in trying to get an auto
incrementing number that is persistent between uses of the workbook:
#1 - determining where to store the value that is the basis for the next
number in sequence, and
#2 - determining/controlling exactly when that number is incremented.

It would appear that it takes some VBA code to provide this functionality.


"lizard1socal" wrote:

isnt there an easier way to add a formula into the cell where the invoice
number is shown on the template to create auto numbering ? Real confused
here, looks like programing to me. Thought Excel was already complete with
automating codes for this sort of problem ?
--
lizard1socal


"JLatham" wrote:

Aha!
In the definition for Const sDEFAULT_PATH, get rid of the < and at the
beginning and end of the string - they should not be in there. It should
look like this:

Const sDEFAULT_PATH As String = "C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"

Plus, you remember that your own SetUpNewClient routine has to be run from
Tools | Macro | Macros

I messed with this some, and tried to 'read your mind' a little, I may have
failed at that. I'm assuming that a customer/client's name is going to be in
E5 or else E5 may be totally empty. This code takes both cases into account.

I moved the Const declarations out of the sub into the body of the module
and made them Public Constants so that both routines can reference them to
get their values.

I didn't change too much in the Function but I beat up on the Sub a bit to
get it to decide whether to use whatever is in E5 as the basis for a
filename, to see if it exists already (existing customer) or if this is a new
customer and starting from ground zero. Hopefully the comments will explain
it all well enough.

Here goes - replaces all that you currently have (the board will rpobably
wreak havoc on some of the longer lines of code, watch out - like your path
should all be on a single line, the rest I think I broke properly) Just in
case, I uploaded a working version that you can get he
http://www.jlathamsite.com/uploads/for_pammij.xls


Public Const sDEFAULT_PATH As String _
= "C:\Documents and Settings\Compaq_Owner\My Documents\Garage Repair
Service\Invoices\"
Public Const sDEFAULT_FNAME = "inv no.txt"

Public Function NextSeqNumber(Optional sFileName As String, _
Optional nSeqNumber As Long = -1) As Long
Dim nFileNumber As Long

nFileNumber = FreeFile
If IsMissing(sFileName) Or sFileName = "" Then
sFileName = sDEFAULT_FNAME
End If
If InStr(sFileName, Application.PathSeparator) = 0 Then
sFileName = sDEFAULT_PATH & Application.PathSeparator _
& sFileName
End If
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
Dim ClientInvoiceNumberFile As String
Dim SequenceNumberMinus1 As Long
Dim sFileName As String
Dim nFileNumber As Integer

With ThisWorkbook.Sheets(1)
If IsEmpty(.Range("E5")) Then
'will force read from default .txt file
ClientInvoiceNumberFile = sDEFAULT_FNAME
Else
'will read from specified file
ClientInvoiceNumberFile = .Range("E5") & ".txt"
End If
sFileName = sDEFAULT_PATH & Application.PathSeparator _
& ClientInvoiceNumberFile
If Dir$(sFileName) = "" Then
'file does not exist, New Customer!
'create unique file for them
nFileNumber = FreeFile()
Open sFileName For Output As nFileNumber
Print #nFileNumber, 849 ' to start at 850 for new customers
Close #nFileNumber
Else
'the file exists and so we
'presume it is an existing customer with
'existing invoice number in it
'and we DO NOTHING
End If
'now we have a file that exists and
'it has some number in it
'call the function to do the work
.Range("B2").Value = NextSeqNumber(sFileName, -1)
End With
End Sub



"Pammi J" wrote:

Hi and many thanks for your quick reply.
I have done that and saved my invoice as a template but when i open there
is no number in my e5 cell.
Here is the code i put in
Have i done something wrong - my macro security is med and firewall ect is
not preventing macros from running.

Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "<C:\Documents and
Settings\Compaq_Owner\My Documents\Garage Repair Service\Invoices"
Const sDEFAULT_FNAME As String = "inv no.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function

Public Sub SetUpNewClient()
With ThisWorkbook.Sheets(1)
.Range("B2").Value = NextSeqNumber(.Range("E5").Value & ".txt",
849)
End With
End Sub

"JLatham" wrote:

Sounds like your problem is with the two Const statements at the beginning of
the function?
-----
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
-----

The first one should be set to the path to where the text file containing
the sequence of numbers is kept. The "<your path here" part should actually
look something like this:
"C:\Documents and Settings\PammiJ\My Documents\"

For the second line, yes you need to create a file somewhere (in the folder
pointed to by the first line) and all it needs to contain initially is the
first invoice number you want to use, minus 1.

Create the file using Notepad: open Notepad, start a new file, just type a
number into it (assume you want invoices to start at 1, type 0 into it).
Then use Save As to give it a name like the one he suggested or you could
give it some other name like PammiJs_InvoiceNumbers.txt and save it in the
path you have decided on. Close Notepad.

Now your first two lines in that function should look something like this:
Const sDEFAULT_PATH As String = "C:\Documents and Settings\PammiJ\My
Documents\"
Const sDEFAULT_FNAME As String = "PammiJs_InvoiceNumbers.txt"

I think the McGimpsey page uses cell B2 as the one where the invoice number
is to appear, just change "B2" in his sample code to "E5" and it should work.

Only thing that might keep it from working is your Macro Security - make
sure macros are permitted to run - best setting that's a balance of security
and convenience is MEDIUM where you are prompted when the workbook is opened
whether or not to let macro code execute. That will keep you from being
unpleasantly surprised if someone sends you an Excel workbook with code in it
that you weren't expecting.

One other thing that can also keep macros from running these days is your
anti-virus application. Some of those have a setting in them that prevents
VBA code from running, and if yours is one of those (I use Kaspersky Business
Optimal and it has such a setting) then you'll need to set it to allow you to
run the code.


"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

JLatham,
Appreciate the reply, although as a simple user didnt understand it
all , but got the jest of what you wrote.
My next question is that , in Excel there is a business invoice
template that has the area for the invoice number, and shows a number in the
template. Now if the template has and shows the number, isnt there someway to
activate that cell with an auto number formula ? ( a new users
question,basically ignorant here )
Thx for your help, undrstanding and patience with us of lessor
knowledge !
LIZARD1SOCAL

Auto Number in Invoice
 

Assuming Invoice numbers are stored in cells A1 through A99, the formula -
=MAX(Data!$A$1:$A$999)+1
will give you the next available Invoice number.

Regards.

Bill Ridgeway
Computer Solutions

Auto Number in Invoice
 

Thats exactly what im after Lizard1socal - exactly what that template does
with the invoice number - unfortunatly i cant get into it - its asking for a
password

"lizard1socal" wrote:

JLatham,
Appreciate the reply, although as a simple user didnt understand it
all , but got the jest of what you wrote.
My next question is that , in Excel there is a business invoice
template that has the area for the invoice number, and shows a number in the
template. Now if the template has and shows the number, isnt there someway to
activate that cell with an auto number formula ? ( a new users
question,basically ignorant here )
Thx for your help, undrstanding and patience with us of lessor
knowledge !
LIZARD1SOCAL

Auto Number in Invoice
 

This works sort of

Private Sub Workbook_Open()
Range("E5") = Range("E5") + 1
End Sub

It does what you want if you open the *.xlt original.
Not if you open a *.xls copy (<File., <New).
Where do I find the McGimpseys code page you are copying from?
Maybe we can both learn something.
Lou

"Bill Ridgeway" wrote:

Assuming Invoice numbers are stored in cells A1 through A99, the formula -
=MAX(Data!$A$1:$A$999)+1
will give you the next available Invoice number.

Regards.

Bill Ridgeway
Computer Solutions

Auto Number in Invoice
 

Pammi J
Check out what Bill Ridgeway erote and tell me what you think ? Sounds
like he may have something !?
--
lizard1socal


"Pammi J" wrote:

Thats exactly what im after Lizard1socal - exactly what that template does
with the invoice number - unfortunatly i cant get into it - its asking for a
password

Auto Number in Invoice
 

Wouldn't the Workbook_Open() event method increment the invoice number in E5
each time you opened the book, i.e., File | Open. And that leads to the
problem of controlling when it increments:

You have a template file .xlt with 0 in E5 you create a new invoice from it
and set invoice number to 1, fill it in and send it out. Later you have to
open up the .xls file and suddenly you realize that the invoice number is 2
-- but darn it, someone else is already using invoice 2 for another sale in
another .xls file. You close your file and open it back up and suddenly it's
invoice #3 in E5.

Somehow you have to control when the invoice number is incremented and when
it isn't. Now, you could modify the Workbook_Open() event to NOT increment
the number if some other information is on the workbook that isn't there in
the 'template' file. Lets say you put the customer name in cell B2, but in
the template that cell is empty. Here's what you'd have to do in the
Workbook_Open() event:
check and see if cell B2 has anything in it, and if it does, don't increment
the invoice number, but if it is empty, then:
increment the Invoice Number in E5
AND
save the 'template file' back out so that the next time you use it, it knows
what the last used invoice number was, and
THEN
give it a new name to be saved under with the new invoice number carved in
stone, so to speak.

This is where the .txt file that McGimpsey uses comes in so handy - the last
invoice number is saved in it, so you don't have to figure out how to do all
of that save, rename, save again type of operation.

It's not just a question of the chicken and the egg, but an added element of
how to tell when the egg will hatch is kind of thrown into it all.

I'm going back and looking at what PammiJ wrote in her last entry here and
see if I cannot work something out based on earlier work plus her added
information.

My question to Bill Ridgeway (not meaning any disrespect or anything like
that at all) is: having come up with a new invoice number fromt he cells in
A1:A999, how do you get that new number into that list? This implies, to me
at least, that somewhere there is a manual operation of entering the last
used invoice number somewhere, and so it's not a totally automatic process,
and is still susceptible to human error (forgetting to update the list of
used invoice numbers).

To give credit to all: it is actually a rather complex issue based on a very
simple need, made so primarily just by the way Excel works.

"Rookie 1st class" wrote:

This works sort of

Private Sub Workbook_Open()
Range("E5") = Range("E5") + 1
End Sub

It does what you want if you open the *.xlt original.
Not if you open a *.xls copy (<File., <New).
Where do I find the McGimpseys code page you are copying from?
Maybe we can both learn something.
Lou

"Bill Ridgeway" wrote:

Assuming Invoice numbers are stored in cells A1 through A99, the formula -
=MAX(Data!$A$1:$A$999)+1
will give you the next available Invoice number.

Regards.

Bill Ridgeway
Computer Solutions

Auto Number in Invoice
 

--
To JLatham and Bill Ridgeway,
It seems that you both know a couple methods that will activate the
auto numbering in an invoice, but which one works in what area of the program
is the question !
My curious is if you call up the basic invoice template and enter the
code or formula into the function/format /formula bar for the cell that the
templates invoice number is displayed in, and save the sheet, wouldnt that
activate that cell to do the autonumbering each time the blank template is
opened ??
Just trying to understand you, without drawing me
pictures !

TX, Lizard1socal

Auto Number in Invoice
 

I believe this is the page being referenced, at least that's where I ended in
doing a search - shows 2 ways, been playing with the 2nd way (in text file vs
in the registry)
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html



"Rookie 1st class" wrote:

This works sort of

Private Sub Workbook_Open()
Range("E5") = Range("E5") + 1
End Sub

It does what you want if you open the *.xlt original.
Not if you open a *.xls copy (<File., <New).
Where do I find the McGimpseys code page you are copying from?
Maybe we can both learn something.
Lou

"Bill Ridgeway" wrote:

Assuming Invoice numbers are stored in cells A1 through A99, the formula -
=MAX(Data!$A$1:$A$999)+1
will give you the next available Invoice number.

Regards.

Bill Ridgeway
Computer Solutions

Auto Number in Invoice
 

Grab this file, hope you have WinZip or something able to open .zip files,
and extract both files in it TO THE SAME FOLDER. The two files, one .txt and
one .xls, must be in the same folder for some features to work.

This will show how it can be done, and I've added lots of comments so
hopefully you'll be able to customize it yourself. If not, ask questions
here or via email and I'll try my best to answer.

In previous book, I misinterpreted what you'd tried and thought there was a
name of some type in E5, not that it was where the invoice number was to be
placed. This fixes that. It's NOT 100% automatic - it is an on-demand
function via macro that grabs a value from the .txt file, increments it by
one and stuffs it into E5 and then also saves that number back to the .txt
file so that the next time you use the macro, whether in that workbook or
another, you'll get the next sequential invoice number. This is all based on
McGimpsey's sample work.

What I've done with it to modify it is to take away the need to define a
default path to the .txt file, although you may do that also, and may want
to. But if no default path is provided, then it gets the .xls file's path
and uses that to go find the InvoiceNumber.txt file - and that's why both
files need to be in the same folder.

http://www.jlathamsite.com/uploads/for_PammiJ.zip
if you get a security alert about a .zip file and aren't permitted to upload
it, try right-clicking the link and using Save Target As.


"Pammi J" wrote:

Thats exactly what im after Lizard1socal - exactly what that template does
with the invoice number - unfortunatly i cant get into it - its asking for a
password

"lizard1socal" wrote:

JLatham,
Appreciate the reply, although as a simple user didnt understand it
all , but got the jest of what you wrote.
My next question is that , in Excel there is a business invoice
template that has the area for the invoice number, and shows a number in the
template. Now if the template has and shows the number, isnt there someway to
activate that cell with an auto number formula ? ( a new users
question,basically ignorant here )
Thx for your help, undrstanding and patience with us of lessor
knowledge !
LIZARD1SOCAL

Auto Number in Invoice
 

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal

Auto Number in Invoice
 

I'm totally uncertain of which of the many Invoice templates you are
referring to. I just opened up Excel 2007 and went to Invoices templates and
there are a dozen or more available. The 3 or 4 that I grabbed and looked at
all had same invoice number: 100, and each one was just simply typed in -
nothing automatic about them at all.

Because I'm unsure which template you're talking about, it's hard to answer
the question in your second paragraph, sorry.

The 'solution' I put up for Pammi J is an on-demand macro coded in VBA.
That way you get a new number when you choose to run the macro, not every
time you open the workbook or when you go to the worksheet.

Now, IF you had a function (VBA code defined as a Function and not as a Sub)
then you could put a formula referring to it in a cell, but here's how that
would work (and you probably wouldn't like it)
if you included the statement
Application.Volatile
in the function code, then EVERY TIME the worksheet was recalculated, the
invoice number would be changed. Now, and this might come close to what you
want - if that line of code was left out of it, then basically it would get
the number one time and never again (I think - that's the way I remember that
type of thing working, but memory could be faulty). However - in either case
you need some place to keep that number so you can grab it and update it for
the next invoice you create. If ALL of your invoices for ALL of your
customers are in the one workbook, then you can figure a way to figure out
which one is the largest invoice number in all of the sheets in your
workbook, OR you can store that rascal out somewhere like in a .txt file, so
that other workbooks can pick up on it and not duplicate invoices when you
use multiple workbooks to keep up with your customer invoicing.

As for activating/updating something like that based on an event like
opening the workbook or activating a sheet, that's what the various Workbook_
and Worksheet_ event routines are there to be used for. The developer gets
to pick and choose where to put the code (as I chose neither of the above,
and just put it in a regular non-event-associated Sub type of macro) based on
the requirements for functionality of the workbook or that feature.

I hope that sheds some light on things for you.

Things you have to keep in mind:
If I have a routine that works everytime a workbook is opened, without
restraint, then it's going to do that. So if the routine says "take the
value in A1 and increment it by 1 and stuff it back into A1" then every time
that workbook is opened, that value is going to increment, even if you really
didn't want it to.

If you have a routine that says go find the largest invoice number used in
the past and increment it by one when I activate (go to) a sheet and stick
that into cell A1 on that sheet, then the numbers are going to change on each
sheet each time you take a look at it. Now, if you restrain those with some
code that says "if there's already a value in A1, don't do anything, but if
there is not, then find the largest invoices previously used and add 1 to
that and put it into A1" then you're pretty stable in your invoice numbering,
but you have to figure out where to keep that last invoice number used; that
can either be in the same workbook (you could even figure it out by looking
at A1 of each sheet in code and saving the largest number found) or in a
central location in the workbook to be picked up and used and recorded for
later use.

But again it's all about control and implementation of such a feature. And
that's what this exercise is all about. Again, this is an area that Excel is
weak (to the point of starvation) in - it has no built in ability to deal
with something like this, and it's up to the developer to design and
implement the solution based on the requirements. There are probably 500
ways to do this. We've only looked at one or two in this thread. Heck, I
can envision someone writing a routine that worked through the Worksheets
collection and found the sequence in it that a sheet is in it and using that
sequence number as the invoice number to put on it. But there are problems
there if you delete sheets or add ones that aren't to be invoice sheets.

"lizard1socal" wrote:


--
To JLatham and Bill Ridgeway,
It seems that you both know a couple methods that will activate the
auto numbering in an invoice, but which one works in what area of the program
is the question !
My curious is if you call up the basic invoice template and enter the
code or formula into the function/format /formula bar for the cell that the
templates invoice number is displayed in, and save the sheet, wouldnt that
activate that cell to do the autonumbering each time the blank template is
opened ??
Just trying to understand you, without drawing me
pictures !

TX, Lizard1socal

Auto Number in Invoice
 

Grab the .zip file I uploaded for Pammi J he
http://www.microsoft.com/office/comm...c-bd3aeb241c30

that will give you one way to do it. You can change the reference to cell
E5 in the code to whatever cell on a worksheet you'd like the Invoice number
to show up in. By revising the InvoiceNumber.txt file, you can even set your
own starting number for your first invoice.

"lizard1socal" wrote:

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal

Auto Number in Invoice
 

J, Liz & Pam My method works if not a template or you reopen the template
(negates purpose of template). You must open the document for it to add a 1.
Opening a copy (<File, <New) of a Template does NOT open the original
document. Therefore it never increments. I suspect that is also true of
McGimpsey link. Dave HELP!!!
Lou

"JLatham" wrote:

Grab the .zip file I uploaded for Pammi J he
http://www.microsoft.com/office/comm...c-bd3aeb241c30

that will give you one way to do it. You can change the reference to cell
E5 in the code to whatever cell on a worksheet you'd like the Invoice number
to show up in. By revising the InvoiceNumber.txt file, you can even set your
own starting number for your first invoice.

"lizard1socal" wrote:

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal

Auto Number in Invoice
 

Would you like me to go back through the method you were investigating and
check anything out? I suspect that the way you describe it as working may be
by intent - create a new file from the template, set up what ever individual
info is needed to get started, close it down and then next time you open it,
it starts incrementing? That kind of operation would be ok for a setup where
you were just printing the created invoice, filling in unique information
each time to print out a new one. The auto-incrementing of it on each open
would be troublesome if you were also trying to keep copies of the invoices
as part of your workbook.

"Rookie 1st class" wrote:

J, Liz & Pam My method works if not a template or you reopen the template
(negates purpose of template). You must open the document for it to add a 1.
Opening a copy (<File, <New) of a Template does NOT open the original
document. Therefore it never increments. I suspect that is also true of
McGimpsey link. Dave HELP!!!
Lou

"JLatham" wrote:

Grab the .zip file I uploaded for Pammi J he
http://www.microsoft.com/office/comm...c-bd3aeb241c30

that will give you one way to do it. You can change the reference to cell
E5 in the code to whatever cell on a worksheet you'd like the Invoice number
to show up in. By revising the InvoiceNumber.txt file, you can even set your
own starting number for your first invoice.

"lizard1socal" wrote:

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal

Auto Number in Invoice
 

I AM WONDERING WHAT HAPPENED TO PAMMYJ. DID SHE GET HER PROBLEM RESOLVED OR
FALL OFF THE PLANET ? PAMMYJ YOU OUT THERE ?? WHAT WERE YOUR RESULTS ??
LIZARD1SOCAL
--
lizard1socal

Auto Number in Invoice
 

We probably scared her off, or she felt the thread was hijacked with
questions you asked, or she may have gone off-line like you and I did, but
with Rookie 1st class, to deal with it all one-on-one.

By now you should have the two working invoices with semi- and fully-
automatic numbering. Perhaps some feedback on those once you've had a chance
to evaluate them might help. If it turns out you think those are handling
things properly, perhaps Pammi J would like to look at them also.

"lizard1socal" wrote:

I AM WONDERING WHAT HAPPENED TO PAMMYJ. DID SHE GET HER PROBLEM RESOLVED OR
FALL OFF THE PLANET ? PAMMYJ YOU OUT THERE ?? WHAT WERE YOUR RESULTS ??
LIZARD1SOCAL
--
lizard1socal

Auto Number in Invoice
 

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Auto Number in Invoice
 

Rookie 1st class
your solution does work but only if you open the same as last invoice - it
doesnt work off just the original template. which is a shame i thought we had
got it sorted then. I need to be able to open a clean invoice from template
each time (other than the incrementing invoice no) as i have to add customer
details to each one.
im going to have a play around with JLatham zip file now and will let you
know how i get on.

"JLatham" wrote:

Would you like me to go back through the method you were investigating and
check anything out? I suspect that the way you describe it as working may be
by intent - create a new file from the template, set up what ever individual
info is needed to get started, close it down and then next time you open it,
it starts incrementing? That kind of operation would be ok for a setup where
you were just printing the created invoice, filling in unique information
each time to print out a new one. The auto-incrementing of it on each open
would be troublesome if you were also trying to keep copies of the invoices
as part of your workbook.

"Rookie 1st class" wrote:

J, Liz & Pam My method works if not a template or you reopen the template
(negates purpose of template). You must open the document for it to add a 1.
Opening a copy (<File, <New) of a Template does NOT open the original
document. Therefore it never increments. I suspect that is also true of
McGimpsey link. Dave HELP!!!
Lou

"JLatham" wrote:

Grab the .zip file I uploaded for Pammi J he
http://www.microsoft.com/office/comm...c-bd3aeb241c30

that will give you one way to do it. You can change the reference to cell
E5 in the code to whatever cell on a worksheet you'd like the Invoice number
to show up in. By revising the InvoiceNumber.txt file, you can even set your
own starting number for your first invoice.

"lizard1socal" wrote:

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal

Auto Number in Invoice
 

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Auto Number in Invoice
 

OK we may be getting somewhere - i have been twiddling with the code and in
the `this workbook` iv pasted this
Private Sub Workbook_Open()
SetUpNewClient
End Sub
Now that when the workbook is opened auto runs the maro and starts a new
number on a blank invoice. --- One problem i cant save the open invoice - i
needed to be able to file - save copy as - and then it would have that unique
inv number along with all the cust details. but when i save it increments on
the invoice number again - any suggestions from here please?
many thanks

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Auto Number in Invoice
 

Pammi J,
Maybe download, unzip and look at the two files in this
http://www.jlathamsite.com/uploads/invoices.zip

Two slightly different versions of the same invoice workbook. Each is
'self-contained' - there is no .txt file or such with either one. I call one
semi-automatic, the other fully-automatic. Each uses a name stored in the
workbook itself to hold the last used invoice number. In the semi-automatic
version, you double-click in cell M3 on the invoice sheet to get a new
number. In the fully automatic version, you make a copy of a 'template'
sheet that is in it and when the copy is made it is given the next number in
sequence. It really is pretty much that simple for each.

There is one macro in both that is visible with Tools | Macro | Macros that
is a setup form to allow you to set the initial invoice number with, or
change the next number to use if you wish to later. In both files there is
one code module that you can copy from into any other workbook and it should
work fine, the code is not workbook or worksheet dependent and will
automatically create the 'name' used for you in a new book if it does not
already exist when you first start using it.

There is the userform in each, which you can use File | Export and File |
Import from the VB Editor to move from one workbook to another.

In the semi-automatic version there's a little coding in the Workbook code
segment to be copied to any new book, with a change needed to indicate which
cell is to have the invoice numbers placed into it (your E5 - right now it is
at M3). In the fully automatic version there's no workbook code, but the
worksheet(s) have some code in them, same deal: change M3 to point to the
proper cell to receive the invoice number.

If you like the way either of them works and have trouble porting the code
over to the invoice file you are trying to get set up, I'd be more than happy
to help: do as lizard1socal did - send email to HelpFrom @ jlathamsite.com
with your Excel file attached and I'll dump the needed pieces into it and
return it to you.

Anyone else is welcome to download that .zip file while it's still on the
site, the original file is (apparently) one of the invoice templates provided
with Excel 2000, and the code is like any code put up here by me: free to use
if it helps you, just don't try making $$ off of my work without cutting me
in on the deal <g.

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Auto Number in Invoice
 

Semi-Automatic version - double-click in a specific cell to get a new invoice
number.

The code for ThisWorkbook:
-----

Private Sub Workbook_SheetBeforeDoubleClick( _
ByVal Sh As Object, _
ByVal Target As Range, _
Cancel As Boolean)

If Not Intersect(Target, Range("M3")) Is Nothing Then
Target.Value = IncrementInvoiceNumber
Cancel = True
End If

End Sub

------
The code for the code module
------

Public Function InitializeInvoiceNumberBase() As Boolean
Dim anyInvoiceNumber As String

On Error Resume Next
anyInvoiceNumber = Names("LastUsedInvoiceNumber").RefersTo
If Err < 0 Then
'doesn't exist create it
ActiveWorkbook.Names.Add Name:="LastUsedInvoiceNumber", _
RefersToR1C1:="=0"
Err.Clear
End If
On Error GoTo 0
End Function

Public Function IncrementInvoiceNumber() As Long
InitializeInvoiceNumberBase ' in case it doesn't exist
IncrementInvoiceNumber = GetInvoiceNumber + 1
UpdateInvoiceNumber IncrementInvoiceNumber
End Function

Public Function UpdateInvoiceNumber(newNumber As Long) As Boolean
InitializeInvoiceNumberBase ' in case it doesn't exist
Names("LastUsedInvoiceNumber").RefersToR1C1 = "=" & Trim(Str(newNumber))
UpdateInvoiceNumber = True ' successful
End Function

Public Function RollbackInvoiceNumber() As Long
'this is not currently used, but
'provided for anyone who wants to implement roll-back
InitializeInvoiceNumberBase ' in case it doesn't exist
RollbackInvoiceNumber = GetInvoiceNumber - 1
'prevent going into negative numbers
If RollbackInvoiceNumber < 0 Then
RollbackInvoiceNumber = 0
End If
UpdateInvoiceNumber RollbackInvoiceNumber
End Function

Private Function GetInvoiceNumber() As Long
Dim tmpString As String
tmpString = Names("LastUsedInvoiceNumber").RefersTo
GetInvoiceNumber = Val(Right(tmpString, Len(tmpString) - 1))
End Function

Sub OpenInvoiceNumberManager()
InvoiceNumberMgr.Show
End Sub

-----

Finally, there is a userForm named InvoiceNumberMgr, it has 1 text box and 3
command buttons. The text box name is txtFirstInvoiceNumber.
The 3 command buttons have names of:
cmd_Update
cmdClose
cmdResetForm


This is the code that goes with the form:
-----

Private Sub cmd_Update_Click()
Dim StartingNumber As Long

If Me!txtFirstInvoiceNumber.Text = "" Then
MsgBox "You must provide a starting/new invoice number."
Me!txtFirstInvoiceNumber.SetFocus
Exit Sub
End If
'is entry in txtInvoiceNumberCell a valid address?
StartingNumber = Abs(Int(Val(Me!txtFirstInvoiceNumber.Text)))
StartingNumber = StartingNumber - 1
If StartingNumber < 0 Then
StartingNumber = 0
End If
'actually set the invoice number value
UpdateInvoiceNumber StartingNumber
Me.Hide
End Sub

Private Sub cmdClose_Click()
Me.Hide
End Sub

Private Sub cmdResetForm_Click()
Me!txtFirstInvoiceNumber.Text = ""
Me!txtFirstInvoiceNumber.SetFocus
End Sub


"JLatham" wrote:

Pammi J,
Maybe download, unzip and look at the two files in this
http://www.jlathamsite.com/uploads/invoices.zip

Two slightly different versions of the same invoice workbook. Each is
'self-contained' - there is no .txt file or such with either one. I call one
semi-automatic, the other fully-automatic. Each uses a name stored in the
workbook itself to hold the last used invoice number. In the semi-automatic
version, you double-click in cell M3 on the invoice sheet to get a new
number. In the fully automatic version, you make a copy of a 'template'
sheet that is in it and when the copy is made it is given the next number in
sequence. It really is pretty much that simple for each.

There is one macro in both that is visible with Tools | Macro | Macros that
is a setup form to allow you to set the initial invoice number with, or
change the next number to use if you wish to later. In both files there is
one code module that you can copy from into any other workbook and it should
work fine, the code is not workbook or worksheet dependent and will
automatically create the 'name' used for you in a new book if it does not
already exist when you first start using it.

There is the userform in each, which you can use File | Export and File |
Import from the VB Editor to move from one workbook to another.

In the semi-automatic version there's a little coding in the Workbook code
segment to be copied to any new book, with a change needed to indicate which
cell is to have the invoice numbers placed into it (your E5 - right now it is
at M3). In the fully automatic version there's no workbook code, but the
worksheet(s) have some code in them, same deal: change M3 to point to the
proper cell to receive the invoice number.

If you like the way either of them works and have trouble porting the code
over to the invoice file you are trying to get set up, I'd be more than happy
to help: do as lizard1socal did - send email to HelpFrom @ jlathamsite.com
with your Excel file attached and I'll dump the needed pieces into it and
return it to you.

Anyone else is welcome to download that .zip file while it's still on the
site, the original file is (apparently) one of the invoice templates provided
with Excel 2000, and the code is like any code put up here by me: free to use
if it helps you, just don't try making $$ off of my work without cutting me
in on the deal <g.

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Auto Number in Invoice
 

The fully automatic version.

Code for the code module is the same as the other above.

Code for the user Form is the same as the other above.

There is no code associated with ThisWorkbook, but instead there is code to
go with the worksheet(s). Also, the basic sheet from which all other invoice
sheets must be referenced in the code along with which cell is to get the
invoice number on new sheets created from it. In the 'template' sheet, that
cell should remain empty.

Private Sub Worksheet_Activate()
'change "Invoice Number" to the name you give to
'your own 'template' sheet in the workbook
If ActiveSheet.Name = "Invoice Number" Then
Exit Sub ' do not put number on basic template sheet
End If
'change "M3" to what ever cell on your sheet(s) is to
'contain the invoice number
'make certain this cell is empty on your template sheet
If IsEmpty(Range("M3")) Then
Range("M3") = IncrementInvoiceNumber
End If
End Sub


to use this version, simply select the 'template' sheet and use
Edit | Move or Copy Sheet
and be sure to check the box next to [Create a Copy]
The new sheet will have next invoice number in sequence in it.

Alternatively, you can right-click on that sheet's tab and choose [Move or
Copy] from the popup list and continue from there.


"JLatham" wrote:

Pammi J,
Maybe download, unzip and look at the two files in this
http://www.jlathamsite.com/uploads/invoices.zip

Two slightly different versions of the same invoice workbook. Each is
'self-contained' - there is no .txt file or such with either one. I call one
semi-automatic, the other fully-automatic. Each uses a name stored in the
workbook itself to hold the last used invoice number. In the semi-automatic
version, you double-click in cell M3 on the invoice sheet to get a new
number. In the fully automatic version, you make a copy of a 'template'
sheet that is in it and when the copy is made it is given the next number in
sequence. It really is pretty much that simple for each.

There is one macro in both that is visible with Tools | Macro | Macros that
is a setup form to allow you to set the initial invoice number with, or
change the next number to use if you wish to later. In both files there is
one code module that you can copy from into any other workbook and it should
work fine, the code is not workbook or worksheet dependent and will
automatically create the 'name' used for you in a new book if it does not
already exist when you first start using it.

There is the userform in each, which you can use File | Export and File |
Import from the VB Editor to move from one workbook to another.

In the semi-automatic version there's a little coding in the Workbook code
segment to be copied to any new book, with a change needed to indicate which
cell is to have the invoice numbers placed into it (your E5 - right now it is
at M3). In the fully automatic version there's no workbook code, but the
worksheet(s) have some code in them, same deal: change M3 to point to the
proper cell to receive the invoice number.

If you like the way either of them works and have trouble porting the code
over to the invoice file you are trying to get set up, I'd be more than happy
to help: do as lizard1socal did - send email to HelpFrom @ jlathamsite.com
with your Excel file attached and I'll dump the needed pieces into it and
return it to you.

Anyone else is welcome to download that .zip file while it's still on the
site, the original file is (apparently) one of the invoice templates provided
with Excel 2000, and the code is like any code put up here by me: free to use
if it helps you, just don't try making $$ off of my work without cutting me
in on the deal <g.

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Auto Number in Invoice
 

Hi
i have emailed you my inv template. The only thing i cannot get to work is
to save a copy of the template with inv no and cust details - it seems to
carry the macro with it - is there any way to save a copy without the macro,
so it keeps the details and inv no given?

"JLatham" wrote:

The fully automatic version.

Code for the code module is the same as the other above.

Code for the user Form is the same as the other above.

There is no code associated with ThisWorkbook, but instead there is code to
go with the worksheet(s). Also, the basic sheet from which all other invoice
sheets must be referenced in the code along with which cell is to get the
invoice number on new sheets created from it. In the 'template' sheet, that
cell should remain empty.

Private Sub Worksheet_Activate()
'change "Invoice Number" to the name you give to
'your own 'template' sheet in the workbook
If ActiveSheet.Name = "Invoice Number" Then
Exit Sub ' do not put number on basic template sheet
End If
'change "M3" to what ever cell on your sheet(s) is to
'contain the invoice number
'make certain this cell is empty on your template sheet
If IsEmpty(Range("M3")) Then
Range("M3") = IncrementInvoiceNumber
End If
End Sub


to use this version, simply select the 'template' sheet and use
Edit | Move or Copy Sheet
and be sure to check the box next to [Create a Copy]
The new sheet will have next invoice number in sequence in it.

Alternatively, you can right-click on that sheet's tab and choose [Move or
Copy] from the popup list and continue from there.


"JLatham" wrote:

Pammi J,
Maybe download, unzip and look at the two files in this
http://www.jlathamsite.com/uploads/invoices.zip

Two slightly different versions of the same invoice workbook. Each is
'self-contained' - there is no .txt file or such with either one. I call one
semi-automatic, the other fully-automatic. Each uses a name stored in the
workbook itself to hold the last used invoice number. In the semi-automatic
version, you double-click in cell M3 on the invoice sheet to get a new
number. In the fully automatic version, you make a copy of a 'template'
sheet that is in it and when the copy is made it is given the next number in
sequence. It really is pretty much that simple for each.

There is one macro in both that is visible with Tools | Macro | Macros that
is a setup form to allow you to set the initial invoice number with, or
change the next number to use if you wish to later. In both files there is
one code module that you can copy from into any other workbook and it should
work fine, the code is not workbook or worksheet dependent and will
automatically create the 'name' used for you in a new book if it does not
already exist when you first start using it.

There is the userform in each, which you can use File | Export and File |
Import from the VB Editor to move from one workbook to another.

In the semi-automatic version there's a little coding in the Workbook code
segment to be copied to any new book, with a change needed to indicate which
cell is to have the invoice numbers placed into it (your E5 - right now it is
at M3). In the fully automatic version there's no workbook code, but the
worksheet(s) have some code in them, same deal: change M3 to point to the
proper cell to receive the invoice number.

If you like the way either of them works and have trouble porting the code
over to the invoice file you are trying to get set up, I'd be more than happy
to help: do as lizard1socal did - send email to HelpFrom @ jlathamsite.com
with your Excel file attached and I'll dump the needed pieces into it and
return it to you.

Anyone else is welcome to download that .zip file while it's still on the
site, the original file is (apparently) one of the invoice templates provided
with Excel 2000, and the code is like any code put up here by me: free to use
if it helps you, just don't try making $$ off of my work without cutting me
in on the deal <g.

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Auto Number in Invoice
 

Pammi J,

Disregard the _Rev02 that you received today - contains 3 'show stopper'
errors in code. My apologies - that's what I get for trying to rush
something out the door without adequate testing. Have repaired the code,
will get working version to you this evening, along with suggestions for how
to set up your customer list for ease of data entry.

JLatham

"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

JLatham
Is the one that you are refering to also the same as one of the ones
that I also have ? Now Im wondering which one that is ???????
KA - BOOM ----- \ (---)==< - - -(~,~ )< # **
-- { } Q

lizard1socal


"JLatham" wrote:

Pammi J,

Disregard the _Rev02 that you received today - contains 3 'show stopper'
errors in code. My apologies - that's what I get for trying to rush
something out the door without adequate testing. Have repaired the code,
will get working version to you this evening, along with suggestions for how
to set up your customer list for ease of data entry.

JLatham

"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

Pammi
I told you I was beyond my comfort level. Was your problem solved? If not
Get Dave Peterson's attention someway (FLASH) and it will.

"lizard1socal" wrote:

JLatham
Is the one that you are refering to also the same as one of the ones
that I also have ? Now Im wondering which one that is ???????
KA - BOOM ----- \ (---)==< - - -(~,~ )< # **
-- { } Q

lizard1socal


"JLatham" wrote:

Pammi J,

Disregard the _Rev02 that you received today - contains 3 'show stopper'
errors in code. My apologies - that's what I get for trying to rush
something out the door without adequate testing. Have repaired the code,
will get working version to you this evening, along with suggestions for how
to set up your customer list for ease of data entry.

JLatham

"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

I personally think you were doing great. I seem to have kind of hijacked
this, and I apologize if I offended you in any way. Didn't mean to be rude
at all, just trying to also offer some assistance to the both of you.

She and I exchanged some emails and fine-tuned her needs. I sent her a
package with workbook and even a bit of a 'User's Guide' along about the
12th, but I have not heard back from her whether or not it fit the bill or
not.

It ended up being based on the McGintey setup with a .txt file used to hold
the last used Invoice number. So when the template file is opened it grabs
the last number out from that file (it's the only number in it-a one-entry
..txt file), increments it, puts it on the invoice sheet and writes that
number back out to the text file. The "control" in it is that if the cell
(E5 in this case) on the invoice sheet is not empty, then no new number is
created or assigned, so after creating an invoice sheet, the file can be
reopened later without messing up the assigned invoice number or the invoice
number sequence in general. Obviously, in the template file, that cell
remains empty.

The way she does her work, as I understand it, is that she used the template
file to create a single invoice for a customer and then that file is saved
individually on the system. Yes, at the end of some period of time, she may
have multiple invoice files even for the same customer, rather than all in a
single book for either the individual customers or for all customers. But
this appears to be an effective way for them to work - and that's what
matters.

"Rookie 1st class" wrote:

Pammi
I told you I was beyond my comfort level. Was your problem solved? If not
Get Dave Peterson's attention someway (FLASH) and it will.

"lizard1socal" wrote:

JLatham
Is the one that you are refering to also the same as one of the ones
that I also have ? Now Im wondering which one that is ???????
KA - BOOM ----- \ (---)==< - - -(~,~ )< # **
-- { } Q

lizard1socal


"JLatham" wrote:

Pammi J,

Disregard the _Rev02 that you received today - contains 3 'show stopper'
errors in code. My apologies - that's what I get for trying to rush
something out the door without adequate testing. Have repaired the code,
will get working version to you this evening, along with suggestions for how
to set up your customer list for ease of data entry.

JLatham

"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks

Auto Number in Invoice
 

No offense taken. I have 30+ templates and thought I had a grip on the
problem just not sure about the solution. I am sporatic about visiting the
site and Thank you for lending a hand. I'll file your name as one of my go-to
guys.
Lou

"JLatham" wrote:

I personally think you were doing great. I seem to have kind of hijacked
this, and I apologize if I offended you in any way. Didn't mean to be rude
at all, just trying to also offer some assistance to the both of you.

She and I exchanged some emails and fine-tuned her needs. I sent her a
package with workbook and even a bit of a 'User's Guide' along about the
12th, but I have not heard back from her whether or not it fit the bill or
not.

It ended up being based on the McGintey setup with a .txt file used to hold
the last used Invoice number. So when the template file is opened it grabs
the last number out from that file (it's the only number in it-a one-entry
.txt file), increments it, puts it on the invoice sheet and writes that
number back out to the text file. The "control" in it is that if the cell
(E5 in this case) on the invoice sheet is not empty, then no new number is
created or assigned, so after creating an invoice sheet, the file can be
reopened later without messing up the assigned invoice number or the invoice
number sequence in general. Obviously, in the template file, that cell
remains empty.

The way she does her work, as I understand it, is that she used the template
file to create a single invoice for a customer and then that file is saved
individually on the system. Yes, at the end of some period of time, she may
have multiple invoice files even for the same customer, rather than all in a
single book for either the individual customers or for all customers. But
this appears to be an effective way for them to work - and that's what
matters.

"Rookie 1st class" wrote:

Pammi
I told you I was beyond my comfort level. Was your problem solved? If not
Get Dave Peterson's attention someway (FLASH) and it will.

"lizard1socal" wrote:

JLatham
Is the one that you are refering to also the same as one of the ones
that I also have ? Now Im wondering which one that is ???????
KA - BOOM ----- \ (---)==< - - -(~,~ )< # **
-- { } Q

lizard1socal


"JLatham" wrote:

Pammi J,

Disregard the _Rev02 that you received today - contains 3 'show stopper'
errors in code. My apologies - that's what I get for trying to rush
something out the door without adequate testing. Have repaired the code,
will get working version to you this evening, along with suggestions for how
to set up your customer list for ease of data entry.

JLatham

"Pammi J" wrote:

Hi
hope someone can help me. I have an invoice i have designed and the last bit
i need before i use it is to assign a invoice number to the template and have
it automatically increase by one everytime - ie 101 would be 102 on opening
the next time.
i have read McGimpseys page and used the code - i put it in the this
workbook part but its not doing anything. Have i got the right code or do i
need any other bits to go with it? Also i dont understand what `text` file i
need - as its saying to create one? and what is default path as string - is
that where i type where my template is. Sorry im a newbie to invoice
numbering. but i only need this and then i can move on to other things.
many thanks