Invoice# creation - Switching creation from print to when document opens

  • Good day,


    So I'm having a slight issue with the current workbook I have for Excel.


    As it states, when all the required information is inputted in the required highlighted fields and by clicking on the Print Form option, it generates an Invoice number with a specific format of YYDDDDhhmmss


    What I'm trying to do is change the code around so when a user opens the document, an Invoice number automatically generates in the appropriate box without having to click the Print Form button to generate such Invoice number. I've looked around for such viable options but all I was able to stumble across were simple numbers like 001, 002, etc... For this though, it has to remain the same format as listed above.



    Now with my knowledge, I've have some when creating macros and such but never has been my strong key point in the IT world, least with excel it ranges from a 4-6 easily. What I'm asking is if someone can overlook the sheet I have and advise me as to what codes should be changed and as to what exactly.


    I would greatly appreciate it!!

  • Re: Invoice# creation - Switching creation from print to when document opens


    You appear to have a separate routine that generates the invoice number.

    Code
    Private Sub SetNewInvoiceNo()


    You call it from the print form button macro.


    You can remove that call and just call the routine when the workbook opens.

  • Re: Invoice# creation - Switching creation from print to when document opens


    Skywriter,


    Appreciate the info on that.


    Still kind of newbish with Excel myself, Would I rename that call to Workbook_Activate, if I understand that correctly, when the workbook opens it should generate the invoice number then?



    Unless I'm mistaken...

  • Re: Invoice# creation - Switching creation from print to when document opens


    You have a block of code with this title.

    Code
    Private Sub Print_Form()


    Near the very bottom of that block of code just before end sub is this line.

    Code
    Call SetNewInvoiceNo


    That lines causes the code to jump into the procedure that generates your new invoice number. You need to remove that line or it will run when you push the print form button. You said you wanted to generate the invoice when the workbook is opened?


    So remove that line and then in the project explorer you will see "ThisWorkbook", right click on "ThisWorkbook" and choose view code.


    Paste this code into the white window that comes up.


    Code
    Private Sub Workbook_Open()
        Call SetNewInvoiceNo
    End Sub


    Then the code will run when the workbook is opened and generate a new invoice number for that sheet.


    If you see any other code in the window that comes up that has the same title.

    Code
    Private Sub Workbook_Open()


    and there is nothing between the title and end sub, then delete that code, you can't have two subs with the same title.


    If there is code between the title and end sub then you must add that code with the call code so they are both in the same procedure and get rid of the other one so you only have one procedure with all the code in it.

  • Re: Invoice# creation - Switching creation from print to when document opens


    I did some testing I think you need to make one other change.
    Change Private to Public.

    Code
    Private Sub SetNewInvoiceNo()


    Code
    Public Sub SetNewInvoiceNo()
  • Re: Invoice# creation - Switching creation from print to when document opens


    Skywritter,



    I greatly appreciate it. Following these steps and making the necessary changes did the trick. I can't thank you enough on this.


    Looks like I'll have to crack open a book and dig a little deeper myself.



    Appreciate the info and assist again!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!