Posts by Allround_IT_er

    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.

    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



    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!

    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…

    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.

    I still have an issue.

    I would like that the order number appears on every line from the same order.

    Now it puts the order number on the first line only (what I asked initially but I see now that this is wrong).
    When I put the code line into the loop, It creates for every line a new increased number.
    If the order contains 4 order lines, the order number should be the same for the those 4 lines.

    Could some one help me?

    Thx in advance.

    I have a table with multi columns.

    I use a code to transfer all the data from a listbox to an Excel sheet.

    The listbox contains an order from 1 client at the time, with different articles the client ordered.

    Everything is wroking fine except for the order number. If someone orders 4 articles, the code transfers the 4 lines perfectly but puts the order number in the last row and leaves the cells above blank. I would like that the code puts the order number in the first row of the order an leaves the cells below blank.

    This is the code I'm using:

    When I put the code line (with Range (M2) before "Next r", the code puts the order umber behind every line of the order, that is not what I want. The order should only get one order number.

    It doesn't matter where I put the line for the order number, it is always wrong. Could somebody help me please?

    Thanks in advance.


    Thank you for your answer. You were right. I used the .list command and excel did NOT restart after adding a product.

    So your answer solved the problem for me, thanks a lot.



    When I click on the commandbutton (cmdArtToevoegen) the code does add the new articles to my sheet and shows me the form (added, in stead of MsgBox) correctly.

    But my listbox (lstboxArt) on the form (frmArtikels) is acting strange (shows only 3 or 4 entries) and reboots excel.

    What is wrong with my code?

    Thx in advance for any help.

    Hello JD,

    Thanks for your help, much appreciated!

    The problem is solved. Thanks you so much.

    And thanks for the tips regarding my code.

    I still have a lot to learn!

    I will try to change my WITH statments as explain by you. Don't know if I will succeed but I will give it a try

    What do you mean by:


    I suggest rewriting the code so that very little of it runs from the form code page, rather have code written in the modules and called from the form page. This generally allows you to write code that is more flexible and can be called from multiple locations.

    Could you give me an example?

    Thanks in advance

    Hi JD,

    Thanks for wanting to help me! Much appreciated.

    I have found some solutions like detecting if the number if higher than the highest number in a row beginning with a certain number (1,2,3,4,5).
    I have a new problem now, If a user enters a number higher than the highest number in the row, a MsgBox should appear, saying that the number entered is to high and after clicking "OK" on the MsgBox, the backgroundcolor of the TextBox should be a different color (reddish), the value that was entered (number to high) should be selected, so that the user can re-enter a valid number without first deleting the wrong-entered number.

    - Detecting the number works fine

    - Changing the backgroundcolor works fine

    - selecting the wrong-answered number works fine

    It seems not possible to select the TextBox after clicking OK on the MsgBox without hiding the main UserForm, showing the MsgBox, clicking OK and re-show the main UserForm. I have create my own MsgBox, but that didn't help.

    When I enter a valid number, the ListBox shows the correct filter, but the backgroundcolor stays modified and I cannot enter the TextBox again by clicking on it.

    I would like that when a wrong number is entered, a MsgBox (self-made or not) appears, telling the entered number was wrong, click OK on the MsgBox and the "wrong-entered value in the TextBox is selected, and the user can re-enter a correct number (while value is selected, the value is automatically deleted when user enters a new value).

    Any Help would be appreciated. Thx in advance.

    Here is the code that doesn' do as I want (It isn't cleaned yet but it gives an idea:

    In attachment my complete workbook.

    I have a column A, containing 4 digits numbers all starting with 1, 2, 3, 4 or 5.

    The starting number represents departments. So there are 5 departments.

    Every department contains different items. So the numbers go for example from 1001 to 1067, from 2001 to 2134, and so on. The item numbers are stored in column A, the items in column B and the name of the department in column C.

    I have a combobox on a userform. This userform is filled with all the Numbers from column A with this code:

    cboZoekProduct.list = Range("tabel3515").Value

    The user can pick a number from the combobox, but he can also enter a number manually.

    After picking a number from the combobox, a listbox is filled with the three columns.

    When the user enter a number that doesn't exists, i get an error.

    When the user enters a number starting with a "1" that is higher than the highest number in the column starting with a "1", I get an error.

    This is my code that isn't working:

    What is going wrong? Any help would be much appreciated!

    Also the code is put in the afterupdate event, I would like it better in the change event, but I get an error after entering the first digit in the combobox.

    Thx in advance

    Works just fine! Thanks a lot!

    Next to column "A", I have column "B" and "C"

    When a Row is inserted, the number comes automaticly (last number starting with digit "1" +1) but data is also going to column "B" and "C".

    So, I adapted your code where "testdepartement" and "testarticle" will be changed to values from textboxes.

    It works fantastic.

    Thanks again!

    I have a table with three columns. In the first column (A), there is always a number for each row (+300).

    The number go from 1001 to 5999.

    The numbers represent categories:

    1000 = Cat A

    2000 = Cat B

    and so on.

    I want to search in column A where the last number, starting with "1", is situated (rownumber). Then I want to insert a row with a new number starting with "1"

    So, in my column the numbers go like:





    So I want to search for the position between 1079 and 2000 to insert a new row with number "1080" in column "A"

    How can I achieve that with VBA by clicking on a commandbutton on a form?

    Thx in advance

    I have a Table containing 3 columns:




    I have a userform for searching an article in the table. Once found, I should be able to input a quantity for that article.

    With a commandbutton, the quantity is entered into the table.

    It should also show the entered line in a listbox on the form until the form is quit. It should show multiple lines, every line that contains a quantity.

    When there is no quantity, it means it has not been ordered, so it should not be shown.

    How can I show only the lines from the table in a listbox on the userform that are containing a quantity?

    This is the code I have so far. The commandbuttom1 action is working but it shows all the rows in stead of only those who have a quantity filled in.

    The rowsource should be unlimited in stead of now "A4:C125".

    I have tried this and it works like a charm! (I hope the expression is correct) :)
    I also understand your explication now. It's a wonderful idea!

    I have another question.
    The worksheet "Vaste bestelling" now only contains the product from the department "Brood".
    It should contain all products of all departments ("Brood, Boterkoeken and Patisserie).
    I have now three departments (multipages) and I have for each department a worksheet.
    On the worksheet "Vaste bestelling" all the product should come together.
    I could easely copy them from the other worksheets but what if I add a product on a department sheet, I will have to add the same product on the worksheet "Vaste bestelling" and determine "y" or "n".
    Is there another way?

    I don't quit understand:


    The whole thing can be made autonomous by adding another sheet with the major/minor product descriptions.That was the reason I added another sheet (even though it only included Breads). The full list would look like:

    BroodCarré brood
    BroodZachte luxe
    PatisserieDrooggebak klein
    PatisserieDrooggebak groot

    That can then be used to to Add tabs to the multipage control for each unique type of product and then frames to each tab for each sub-product, then the code I have already added can be modified to load frame with the correct products.If you add a new product, or even a new product line, then the code will include that as long as you add the new data to the list.You can also remove a line...

    I understand your point regarding multiple products defined by 1 item. It's true, not a good idea.....

    I would appreciate some code to get me started.

    And so many thanks for the code already :)