Excel VBA Userform Date issue

Anonymous
2020-05-12T23:24:57+00:00

I am creating an excel database populated by a userform - the userform can also be re-populated with the data from the spreadsheet for later editing, deletion printing etc.Te issue is that the textbox populates the relevant cells in the correct dd/mm/yyyy UK regional format - however if the data contains a leading xero eg-02/08/2020 the value on the user form is returned as a numerical string - any other dates with a leading number of 1,2 or 3 such 21/09/2020 works fine The spreadsheet cells are all formatted correctly with a data format as above - Ive tried everything I can find and think of - can you advice pleaseABrown

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

62 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-05-16T13:02:20+00:00

    Hi Andreas

    I think im well aware of how to use a change event thank you 

    My question is regarding the code i need on the user form - also the other point regarding the list box please

    Was this answer helpful?

    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2020-05-16T06:50:10+00:00

    is there away to carry out that calculation while the user is inputting the data for each each a new incident record is generated?

    You can use the Change event of a Textbox to execute a code while the user is typing.

    Choose the related Textbox on the left and the Change event on the right drop down.

    Andreas.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-05-15T17:28:46+00:00

    Hi Andreas and Ossie

    Thank you both for assistance

    Andreas - yes I know text was incorrect but that was the point of the original help request because text actully does work up to an extend until you have a date leading with 0 - I posted to the request to seek assistance with that issue which Ossie resolved and correctly in respect of the UK format

    So stepping back - regarding the the post in respect of using passive vba (rather than a command button on the user form) or a formula in the workbook - is there away to carry out that calculation while the user is inputting the data for each each a new incident record is generated?

    Lastly can I create a list on a user form which adds items each a new item is added from a combobox list without creating a complete new record every time a new item is added- thinking of a way to explain - it would be like shopping online where your online shopping basket acts like a list box and then copies all those items when your finished onto the worksheet when you press SAVE

    Was this answer helpful?

    0 comments
  4. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2020-05-15T07:52:59+00:00

    do you get the point ?

    Hi Adrian,

    not really, but that doesn't matter. The point / problem with your database and code is that you have Text instead of real date/time in the cells.

    I know you think it doesn't matter, because the formula in D,F,H,J works... but why is that is a different story.

    Trust me that is an error and it must be corrected:

    Columns E,G,I,K is Text and you have errors:

    On a German system you can see that all dates with a dot as separator are valid, all others are text and wrong and invalid too:

    If you have text in the cells, your file will never work. That is the point.

    Change all date/times to real date/times in the sheet.

    Use Date variables as Ossie suggested

    Use code with CDate and FormatDateTime and write the Date variables into the sheet and your problem is gone.

    Andreas.

    Was this answer helpful?

    0 comments
  5. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2020-05-15T07:22:46+00:00

    My interpretation of the info in the link you provided is that m can be used when preceded with h so when used in combination with hours it is valid. It all works for me that way.

    Your interpretation is correct and yes, it works on most systems.

    I've stumbled upon this problem myself in the past, so I can say that "m" doesn't always work on all systems, but "n" always works.

    I should have said "you should" than "you must", sorry, my bad.

    Andreas.

    Was this answer helpful?

    0 comments