Posts by Allround_IT_er

    I want to use a cell for the input of a search field. However, I want the cell to contain a predefined text namely: +++____/____/______+++ the first section contains 3 digits, the second contains 4 digits, and the third section contains 5 digits. If one clicks in the cell, the user must enter 12 digits in the total: 3, 4 and 5. So 12 in the total that the user enters consecutively. The end result should look like this: +++123/1234/12345+++ I've tried via data validation (via a formula) but I can't find a formula that allows for a predefined text. Thanks for the help.

    Hi Carim

    I don’t understand your post.


    Just a couple of remarks :

    • wk9128 has proposed a Sumproduct function which is already an array formula ... so there is no need for CSE - Ctrl Shift Enter keys in order to enter the formula -
    • when designing an Excel spreadsheet, to be effective, there are 7 golden rules :
      7 Golden Rules for Spreadsheet Design

    Hope this will help

    You are talking about: there is no need for CSE? I didn’t mention or ask anything about CSE?

    The designing of the workbook I have put in attach, isn’t a real workbook, it was just created to illustrate the problem I had. So it isn’t representative.

    I’m sure that wk9128 can figure out the problem I have and can propose an answer which I would really appreciate.

    Hi WK9128,

    The formula you suggested works perfectly! Thank you for that.

    However, I have some questions.

    Why can't the Range (C1:C10) be changed into Range(C1:D10)? When I extend the range to multiple columns, I get an error.
    Is there a solution to extend the Range to multiple columns?
    If any cell is empty in the search range, the result is wrong (See attachement)

    Thanks a lot!

    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