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

  • Go to Best Answer
  • 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.

    • Best Answer

    Hello AL,


    The solution to your 2nd problem is here:

    SetFocus not working on TextBox after AfterUpdate [SOLVED]


    Essentially you need to disable the next textbox in the tab order before setting focus in your code:


    Code
    txtZoekOpArtBenaming.Enabled = False
    txtZoekOpArtNr.SetFocus


    then include a sub that re-enables the disabled textbox when setfocus occurs:


    Code
    Private Sub txtZoekOpArtNr_Enter()
    txtZoekOpArtBenaming.Enabled = True
    End Sub


    This is working for me on the example you sent.


    Please note that placing this second question after your first question is not helpful to the forum, particularly as you have not really shown the solution to your first question. No doubt a moderator will draw your attention to the rules and request you read them again.


    NB: Good luck with the coding, I like the look of the forms. I would suggest the following if you are open to it:

    1.

    You are not always using your WITH statements, once you use a with statement you do not then need to name the object again.

    2.

    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.

    3. you can loop through controls using for example:


    Code
    with frmKlanten
    for i = 1 to 8
    .controls("TextBox" & i).value = ""
    next i
    end with


    Best

    Justin

  • 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

  • Hi AL,


    I have made a small example using your sheet and an alternative approach.

    Note, I have only built the article number textbox and the listbox. I have used this site:

    The VBA Guide To ListObject Excel Tables
    Learn everything there is to know about manipulating and interacting with ListObjects in VBA. Excel Tables are a cornerstone of spreadsheet design. There are…
    www.thespreadsheetguru.com

    when learning to navigate tables in vba.

    NB: As the thread is solved, send me direct if you have any further questions.


    Regards

    Justin

Participate now!

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