Home |
Search |
Today's Posts |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 " |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to do a auto number generater | Excel Worksheet Functions | |||
Generate Auto Order Number | Excel Discussion (Misc queries) | |||
Auto Number | Excel Discussion (Misc queries) | |||
Auto number invoices in Excel | Excel Worksheet Functions | |||
assign auto number and auto date | Excel Discussion (Misc queries) |