Searching on a list box

  • Hello All,


    Need help.


    So I have a UserForm with 2 Listboxes - lstOldCity and lstNewCity.

    lstOldCity - is populated based on the table tblCityListOld.

    lstNewCity - is populated based on the table tblCityListNew.

    Both tables are found on Sheet1.


    As you can see, each city on the table has its corresponding number.

    Basically, what I am trying to do here is search the list based on those numbers.

    To identify on which list I am searching, I have 2 checkboxes which is CheckBox1 and CheckBox2.

    CheckBox1 - will search on lstNewCity.

    CheckBox2 - will search on lstOldCity.


    When searching for lstOldCity, CheckBox2 is TRUE, user will enter a number on TextBox1.

    After entering a number, the result or the city name will automatically come out on Label5.


    When searching for lstNewCity, CheckBox1 is TRUE, user will enter a number on TextBox1.

    After entering a number, the result or the city name will automatically come out on Label5.

    Then on TextBox2, user will enter another number. The numbers are from 0 to 5.

    These numbers has 2 results, Gender and Year of Birth.


    0 - Male - 1900 to 1999

    1 - Female - 1900 to 1999

    2 - Male - 2000 to 2099

    3 - Female - 2000 to 2099

    4 - Male - 2100 to 2199

    5 - Female - 2100 to 2199


    Gender result will be displayed on Label6.

    Year of Birth result will be displayed on Label7.


    Notes:

    • Edit: [I know its easier to just check on the list manually by scrolling, however,] the list of City on these examples are just some of the City I need. I will enter more once I settle the codes.

    • Attached sample workbook.


    Thank you again for the big help!

  • Try the attached file. I have made some changes to your user form to make things a little easier. I hope this is OK with you.

    Files

    • chester.xlsm

      (42.78 kB, downloaded 12 times, last: )

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Actually, I have a follow-up on this. I kinda modify your work to suit my needs.


    1. I noticed that I can only enter 1 number on the search textbox. If I enter more than one the mgbox would come out. I had to remove the msgbox, in order for me to type in 2 or 3 numbers. As I mentioned above, the cities on this sample is incomplete. I have so much more on my original list. Is there a way to fix this? I mean, I like the idea of the messagebox saying there's no match found but I need to be able to enter the full number 1st.


    2. There are cities wherein they have the same #. Is there a way for me to display both cities when their number are searched?


    Thank you again for the help, Mumps !

  • Please attach an updated file that contains data as you described above. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hey Mumps . Here is an updated file from what you made.


    As I said, what you did was already great. May be I am just too noob at this, that's why I cant make the changes I want. LOL.


    So, I updated the 2 tables. As you can see, some numbers have zeroes at the start. This is the way it should be because it is a vital part of what we verify so we can not remove the zeroes.

    Also, there are entries with the same #, this is also a possibility. So, if possible, if their number is searched, I want both results to come out.


    Another thought, would the userform still works if the sheets are protected and table cells are locked?

  • Mumps almost there, man. Everything is working fine, until I noticed that when I search for the very last # on the list, it doesn't give me the right result. It seems that the code is not capturing the last text on the line. I have updated the file here with the complete lists of Cities. Please take a look.

  • The problem is caused by the fact that some of the ID's that have two digits also begin with a zero. For example, the ID for TP Can Tho in column B is 36 and the ID for Nam Dinh in column D is 036. Is there any reason why the same ID number is written with a zero in one column and without the zero in the other column and why ID numbers such as 036 and 095 are not entered as 36 and 95?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • These numbers are unique numbers depending on the type of ID a city has. The numbers from the first list (left) is an older version. And, the other list (right) is the newest version. Newer IDs are required to have 3 digits as opposed to the old one that has either 2 or 3 digit numbers.


    Edit: Unfortunately, it is how its supposed to be. We can not change the numbers assigned to these cities as they are unique.

  • In column B, 090, 091 and 095 appear to follow the newer ID format where all ID's begin with a zero. Are 090, 091 and 095 correct? If they are, why would ID number 36 not be written as 036? I'm sorry for all these questions, but they are necessary. Another question: can an ID have more than 3 digits?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 090, 091 and 095 are correct. Unfortunately, I can not answer this question as I don't know myself. Lol. It must have been because of their geographical location. Believe me when I tell you, I find it weird myself. Haha


    Can an ID have more than 3 digits? For now, No. Unless, they release another set of IDs, which I doubt given the global situation. I am confident we'll stick to 3 digit max.

  • OK. That will make things tricky because for the most part, macros depend on patterns. I'll see what I can do and get back to you.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try the attached file. I've replaced TextBox1 with a combobox from which you can select the ID. This is the only way I could solve the ID format problem. I hope it works for you.

    Files

    • chester3.xlsm

      (61.47 kB, downloaded 12 times, last: )

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.