Posts by chester1993

    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.

    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.

    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.


    • chester2.xlsm

      (56.53 kB, downloaded 31 times, last: )

    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?


    • chester.xlsm

      (33.97 kB, downloaded 33 times, last: )

    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 !

    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.


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

    Hello All,

    Back here again to ask for help.

    So I have sheet1 with a raw data on 2 columns.

    Column B16:B80 is "#" column - it has number from 1 to 65.

    Column C16:C80 is "City" column - contains list of cities.

    Headers are no longer included in that array. I also placed this on a table which is "tblCityListOld".

    I have a ActiveX textbox that will contain the # or City to search. This textbox is linked to E3.

    I also have a search criteria on G3 which is a list (data validation list) of both headers - "#" and "City".

    Results for the search will be displayed on C8:F11.

    Basically, what I initially want is for user to enter either a "#" or "City" on the textbox based on the search criteria.

    Okay, now I tried searching for answers in Google and YT but I didn't found any results that tailored my needs.

    I have found this formula below that almost fit what I need. However, this formula does not work on numbers.

    Do I have any alternative formula I can use to make this search box work?

    Thank you!

    1. =FILTER(tblCityListOld,ISNUMBER(SEARCH(E3;INDIRECT("tblCityListOld[" & G3 & "]"))),"NO MATCH FOUND")

    That's the weird thing royUK . It could have been my mistake not checking for other dates sample. But, the dates I provided on the initial example was working fine with your initial code. I mean, I've been using the same code for some time, and for some reason, it was only today I encounter a difference on the years.

    Man, I am not sure what happened. LOL

    Try this version:

    This one works fine based off the 2 examples I gave thus far.

    Quick question, rory . How is this different from the one roy initially gave me?

    Also, sorry this could be a noob question, but the code below, what does it mean or its function?

    1. .TextBox6 = iYrs - 1

    Hello royUK . Hope all is well.

    Sorry I have to re-open this thread.

    As I said above, your formula works great. But, one of my users encounter a problem with it. Same problem when I open this thread - the date is not calculated properly.

    We were trying to calculate the difference between the date 03/02/2002 and 05/22/2020. On the formula you provided above, the result is 17. When we try calculating using Windows Calculator, the difference was 18.

    Do you have any idea why this happened?

    Thank you!

    Hello Team,

    So I have a Userform that intends to calculate the user age.

    Textbox3 = DOB entered by user

    Textbox4 = Date entered by user

    Commandbutton3 = click to calculate the age

    Results of difference go to TextBox6

    I have the following code:

    1. Private Sub CommandButton3_Click()
    2. With Me
    3. Me.TextBox6.Value = DateDiff("yyyy", CLng(CDate(.TextBox3)), CLng(CDate(.TextBox4)))
    4. End With
    5. End Sub

    The problem with this code is its only calculating the year difference and not exactly what I want.

    For instance, DOB of user is 10/1/2002 and the date on textbox4 is 9/1/2020. One look and you can already see this user is below 18.

    When I calculate using the above formula, it gives me a result of 18 when it should be 17.

    Thank you for any help.

    Hello All,

    So, I am trying to create an hourly tracker. Every hour, I will enter a value on cells B8:AE17 (this is the range where I would enter my numbers).

    Now, I want to identify these numbers based on their color/fill.

    I also 2 have 2 checkboxes that will identify my type of work. Colors vary depending on the value in checkbox 1 and 2.


    Checkbox1(email) is selected.

    I enter a value on 2:00 AM.

    Because my checkbox1 value is true, I want the cell be colored with green.

    The same will happen to the following cells as long as the checkbox is ticked.

    Same scenario for the 2nd checkbox(messaging). Only that color will be blue, for instance.

    I also want to note, I don't want the previous fill/color changed when I change checkboxes.

    Deleting the value on the cell, will remove fill.

    I have tried different methods to do this, but, its just not easy for me. hays.

    Now, I want to try if this can be done on VBA. Or, if you have a better method, I also appreciate it.

    Thank you!


    • Tracker.xlsm

      (22.54 kB, downloaded 37 times, last: )