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
"