Posts by Allround_IT_er

  • Predefined text in a cell

    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.

  • Countifs, count how many times a dates occurs in a range for a customer

    Hi Carim


    I don’t understand your post.

    Quote from Carim

    Hello,


    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.

  • Countifs, count how many times a dates occurs in a range for a customer

    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!

  • Making an invoice by hitting the commandbutton

    Quote from 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.


    Quote from KjBox

    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.


    Quote from KjBox

    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!


    Quote from KjBox

    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.



    Quote from KjBox

    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.


    Quote from KjBox

    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.


    Quote from KjBox

    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.


    Quote from KjBox

    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.

  • Making an invoice by hitting the commandbutton

    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!

  • 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.

  • Can't get a number in the correct cell

    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.

  • Can't get a number in the correct cell

    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.

  • Why does code kicks me out of excel and lets excel reboot?

    Justin,


    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.


    Regards,

    Bart

  • Why does code kicks me out of excel and lets excel reboot?

    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.

  • Error when entering a number in combobox that doesn't exist

    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:

    Quote

    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

  • Error when entering a number in combobox that doesn't exist

    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.

  • Error when entering a number in combobox that doesn't exist

    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:

    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