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
"