Making an invoice by hitting the commandbutton

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    Creating an invoice by the hit of a button
    I have a table, containing orders from different clients, different items, ordered on different days. I would like when the users hits the commandbutton, vba…
    www.mrexcel.com


    I have a table, containing orders from different clients, different items, ordered on different days.

    I would like when the users hits the commandbutton, vba should ask for what client (showing a dropdown with all the clients who have ordered).

    When the user clicks the client, an invoice is made for that client, just 1 invoice/client regardless the dates the client ordered. VBA should count the amounts together.
    An example is attached.


    Any help is much appreciated.

  • Try the attached.


    To create a new invoice simply select a cell in the Client Column of the Table on the Orders sheet.


    Code in Worksheet Object Module for the Orders sheet is

    I have converted the table on the Invoice sheet into an Excel Built-in Table.


    Also assumed that each time an invoice is created the invoice number will increase by 1.

    If that assumption is wrong and you need to enter the invoice manually for each invoice created then change this line of code


    .[c2] = Target: .[c3] = Date: .[c4] = .[c4] + 1


    to


    .[c2] = Target: .[c3] = Date

  • Hi KjBox,



    Thank you for your help but it didn't quit do what I want.

    There is no commandbutton that I can hit, no dropdown with all the clients who have ordered.

    When I run your code through the VBE,

    It does following:

    setting date

    invoiceNo

    Article


    It does not set:

    amount nor count it together

    No price is transferred to the invoice

    No discount is transferred

    Not total has been calculated

    I have seen no Commandbutton to active the making of the invoice.

    There should be a dropdownbox filled with all customers in the order list.


    So, the user should be able to click the dropdownbox, choose a client and make an invoice where the articles that have been ordered on several days but sometimes with the same article number, should be created as ONE single order line by counting the amount together, if the client and the Article number are equal.

    Once an invoive is made, there should be a new colomn that says, "invoice created" and a second colomn with drowdown "YES/NO". If value = Yes then the order line should be completely transferred to an new sheet (Archive invoices)

    Appreciate your help!

  • rory

    Added the Label Cross Post
  • With what I sent the user clicks a Client name in the Table on the Orders sheet and an Invoice is created for that Client doing just as you want.


    When I run your code through the VBE,

    You cannot run a Worksheet_SelectionChange code through VBE. Did you try to copy/paste the code to a Standard Module? That will not work.


    Have you actually tested using the file I sent?


    Why would you want a Combobox created each time you want to create an invoice for a client? Why not just have a Combobox on the sheet anyway, and select a Client from there?


    Seems far simpler to just select a Client directly from the Table on the Orders sheet and create an invoice for that client automatically. That is what the code I supplied does.


    Once an invoive is made, there should be a new colomn that says, "invoice created" and a second colomn with drowdown "YES/NO". If value = Yes then the order line should be completely transferred to an new sheet (Archive invoices)

    Appreciate your help!

    Why was this not mentioned in your original post?


    Where is that column? Why have you not included it in your sample file? Your explanation reads as if you want new "Yes"/"No" column created each time an invoice is needed, which, of course, is totally impractical.


    You mean you want a new sheet created every time an invoice is created? How should the new sheet be named? You would soon end up with a workbooks containing hundreds of sheets, if not thousands!


    I suggest you decide just what it is you want, create a sample workbook that clearly shows how the Orders sheet should be and a separate sheet(s) to show your required result. Then you may get an answer you want.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited 4 times, last by KjBox ().

  • With what I sent the user clicks a Client name in the Table on the Orders sheet and an Invoice is created for that Client doing just as you want.

    You are right! I didn't look very well at it and just run it through the VBE and of course, I didn't see what your code is actually doing.
    Like you said, it works exactly as I asked. Thank you for that. The code you wrote to achieve this is not very clear to me, but that is not absolutely needed.


    you cannot run a Worksheet_SelectionChange code through VBE. Did you try to copy/paste the code to a Standard Module? That will not work.

    I know now, but I didn't knew before. So I've learned again, thank you for that.


    Have you actually tested using the file I sent?

    I did, but not in a proper way. I did test it again now, like you explained in your second answer, and it works beautifully!


    Why would you want a Combobox created each time you want to create an invoice for a client? Why not just have a Combobox on the sheet anyway, and select a Client from there?


    Seems far simpler to just select a Client directly from the Table on the Orders sheet and create an invoice for that client automatically. That is what the code I supplied does.

    Correct, that seems far simpler. Thank you.



    Why was this not mentioned in your original post?

    Because, I thought it would be better to ask little by little in stead of asking a lot at once. I'm sorry.


    Where is that column? Why have you not included it in your sample file? Your explanation reads as if you want new "Yes"/"No" column created each time an invoice is needed, which, of course, is totally impractical.

    In my sample file, the column was not included yet.

    I would like just two additional columns. One with the mention "invoice created" to prevent that the invoice should be created a second time. And a second column with a "yes" choice to move the invoice lines to the sheet archives.


    ou mean you want a new sheet created every time an invoice is created? How should the new sheet be named? You would soon end up with a workbooks containing hundreds of sheets, if not thousands!

    Certainly, there should not be created a new sheet every time an invoice is created.


    I suggest you decide just what it is you want, create a sample workbook that clearly shows how the Orders sheet should be and a separate sheet(s) to show your required result. Then you may get an answer you want.

    The sample I sent was just a sample to see the technique to do what I wanted.

    I will now send my original workbook an a word doc with some explication.


    Your help is much appreciated. Thank you so much.

  • OK, I will look at those files today and come up with something for you.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I sent you a PM

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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