1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: invoice template number update

Discussion in 'Business Applications' started by keithcun, Jan 22, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. keithcun

    keithcun Thread Starter

    Joined:
    Jan 21, 2011
    Messages:
    5
    I have been trying to produce an invoice template which will update the invoice number each time it is opened:-
    ie.

    Open Invoice template numbered 100.
    Enter details
    save as (unders Customers name)
    Close template (without saving)
    Reopen template for new invoice with the number updated to 101.

    I found the following post which is what I am looking for

    http://forums.techguy.org/business-applications/929704-excel-2007-auto-invoice-numbering.html

    but am unable to get Keebellah's solution to work mainly due to the fact I have not enough experience in VBA.

    His solution is exactly what I require.

    Could someone supply VBA code to solve my problem and explain in simple terms how to input it.

    Keebellah has made his solution" read only" to stop anyone changing the layout and this seems an excellent idea.

    Is it possible to also supply VBA to carry this out which I can use when I have made all my ammendments.

    I have attached a copy of my workbook and as you can see I have included an option to update dates through a pop up calendar.

    The invoice cell is M12
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Hi, welcome to the board.

    I saw your post and have dounloaded your sample.
    I'm a little busy right now and will have to refresh on what I did previously but I'm sure we can work it out.
    I'll let you know as soon as I have a sample for you.
     
  3. keithcun

    keithcun Thread Starter

    Joined:
    Jan 21, 2011
    Messages:
    5
    Many thanks.

    I manged to work through your original post and have been able to impliment it in my example.

    The only problem I am having now is that once the solution is imputed the work book becomes "read only".

    This is ideal but is there any way I can temporarily disable the "read only" to make ammendments.

    At present the only way I can do this is to delete your solution, make ammendments and then reload the solution.

    Keith
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Hi Keith,

    I think it's faster if you attach the working example you have so that I can take a look.
    Do you mean the worksheet is read only or the whole workbook?

    It it's the sheet then it's just a macro with the line ActiveSheet.Unprotect to unprotecxt and ActiveSheet.Protect to protect that is if there's no password, else you have to inlcude the password

    I'll take a look when I see the attachment, okay?
     
  5. keithcun

    keithcun Thread Starter

    Joined:
    Jan 21, 2011
    Messages:
    5
    keebellah

    many thanks for your speedy reply.

    It seems to be the workbook which is read only.

    I have attached your original post for your info.

    regards

    Keith
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Okay, I'll tackle it sometime today
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Hi Keith,
    I had to refresh my memory and took a look at your sheet too.
    I editted the code and added my old post's macro code to the 'Hillis Blank-Invoice.xls' file
    What it does now, and this where you will have to see if it's workable for you:
    What this blank file does now when it opens is generate the invoice number. This is stored in a new filetype and named JME-invoicenr.inv
    If this file is not there a new one is created and it starts counting with 001, it allows for 999 invoices for the current year
    If you need more than you have to edit the code in the function GetInvoiceNr() and edite the following line:

    InvNr = (Year(Date) * 1000) + 1 and change it to InvNr = (Year(Date) * 10000) + 1

    Your new invoicenumber will now be 20110001 instead of 2011001
    I also notecd that I don't take into account to check if a new year has started so what you'll have to do is delete the *.inv file at the end of the year after your last invoice has been created and the first one i the new year will be 2012001 or 20120001 (depending on if you cahneg the line in the code)

    Another change: when you open the Hillis Blank-Invoice.xls the file is saved as invoicenr-yy-mm-dd.xls automatically and the the Hillis Blank-Invoice.xls is closed without saving to reuse and generate a new invoice.

    This does does mean however that the new created file does not contain any macro's (just the calendar picker) and will not be saved automatically with a new name if you change the datevalue.

    This could be circumvented by edtting the original blank file and instate the read-only option (to avoid accidents by overwriting) and first enter the invoice sheet completely before svaing.

    Think about it and maybe you (with my help if needed) can reprogram this.

    Try the sample I have attached and let me know.

    BTW the FileSaveAs module allows it to be used with Excel 2007 as well so if the ystsem is upgraded from 2003 to 2007 this will still work (I hope)
     

    Attached Files:

  8. keithcun

    keithcun Thread Starter

    Joined:
    Jan 21, 2011
    Messages:
    5
    Hans

    The number generator seems to be working ok but I have a few problems.

    1. I cannot open JME-invoicenr-inv . I get an error message to say " Windows needs to know what program you want to use to open it".

    2. How do I remove "Read only" in order that I can fill in invoice details on the original form.

    3. I cannot see where you have entered your code.

    I am sorry if these questions seem elementary but this is the first time I have tried to do anything like this in Excel and therefore am a complete novice.

    Regards

    Keith
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    You do not have to open the JME-invoicenr.inv, it's just a text file with the last number in it to increment when you open the workbook, so just don't do anything with it.
    The sample I snet you is NOT read only, what it does, as I explained, is open the workbook, put the invoice number and date in the right place, save it in the same folder the blank file is in, open it and clos the blank file without saving it so you can repate the process for a new invoice.

    I did howver add the note that maybe it has to changed so you can first fill the invoice and then save it after you have completed it, but now you're working with the text file.

    I added the correct Hillis-Blakn-invoice.xls which does what I described above.
    If you detel te JME-invoicenr.inv file it will start with a new invoice nr yyyy001
     

    Attached Files:

  10. keithcun

    keithcun Thread Starter

    Joined:
    Jan 21, 2011
    Messages:
    5
    Many Thanks Hans

    I have been able to get everything working correctly now.

    many thanks for your explanation which now makes sense.

    Thanks for taking the time to help with my problem

    regards

    Keith
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,596
    First Name:
    Hans
    Don't mention it, and excuse me if my explanation sounded a little bitchy, that was not my intention.
    If you need any further help or need some help thinking things out you know where to find me / us.
    Don't forget to press the Mark Solved if it's solved for you.

    Bye for now
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/976323

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice