Posts by chester1993

    Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. We are here to help so help us help you!

    Read this to understand why we ask you to do this


    Thank you for the reminder, I didn't realize it's a thing. Will follow this moving forward.

    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

    Hello everyone. It's nice to be back here. Obviously, I need a big help from this forum, again.

    I am to develop a project that would allow may co-team mates to request for Vacations Leaves via Excel (using a Data Entry form). These request will go directly to a database in Access. Why Access? I thought storing a large amount of "request" in Excel would only increase the file size and make it harder for the codes to execute. If I am wrong here, please educate me a better way of resolving that issue.

    With that being said, the file will be shared AND/OR the users can download the file and save a copy of it on their own desktop. Now, since the requests are in a Access database, again please correct me on this part if I have a misconception, the file, even though it has multiple copies outside and shared to different users, it would update real time on the users end. Am I wrong here? Let me know.

    Another dilemma I have is where to save the database that would allow the Excel files to update real-time. I was thinking of shared drive or folder. Is that a possibility? How about One Drive? Can it do the work?

    The project right now is in a planning phase. I want to sort out this questions first before I start doing it and I think this is the best place to ask for guidance.

    If it helps, I am using an Office 365.

    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 35 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 36 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.