+ Reply to Thread
Results 1 to 121 of 121

VBA User form - date fields convert to US format instead of UK format

  1. #1
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    VBA User form - date fields convert to US format instead of UK format

    Hi there

    I am having issues with my VBA user form where dates are converting to a US format instead of a UK format. e.g. when i enter: 12/05/19 (12 May 2019), this is converted to 05/12/19 (05 December 2019). How can i stop this conversion?

    Thanks
    Motoka
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA User form - date fields convert to US format instead of UK format

    At which point does it do that?

    When user enters it into TextBox? Or when value is transferred to cell?

    VBA by default uses US format. To have user enter date in appropriate format, use custom Calendar form and have textbox show us formatted date (or more universal yyyy-mm-dd format).
    Ex:
    https://trevoreyre.com/portfolio/excel-datepicker/

    Then, when returning value to cell use CDate() to convert string to date value.
    In your table, set number format of the cell to dd/mm/yyyy.

    Edit: Personally, I prefer to enforce yyyy-mm-dd format for data entry. It's accepted as standard date string in various systems (MySQL, T-SQL, Excel etc) and can work across different region settings.
    Last edited by CK76; 09-09-2019 at 01:22 PM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA User form - date fields convert to US format instead of UK format

    The CDate function in VBA is not loyal to date internationalization settings. If you enter a date that can be interpreted using the U.S. format, it will try that first. If not it will try the UK/international style.

    12/05/19 => 04 December 2019 (can be interpreted as U.S. so that's what you get
    18/05/19 => 18 May 2019 (can't be interpreted as U.S. so it tries international)

    Instead of CDate, try this function that I wrote:

    Please Login or Register  to view this content.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Motoka, I noticed jazzer posted some code, does this work for you?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  5. #5
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA User form - date fields convert to US format instead of UK format

    With years of 'date' frustration I use the attached approach when using 'textboxes' on 'userforms'.
    See if you still get your problem when entering dates into form.
    torachan.
    Attached Files Attached Files

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by 6StringJazzer View Post
    The CDate function in VBA is not loyal to date internationalization settings. If you enter a date that can be interpreted using the U.S. format, it will try that first. If not it will try the UK/international style.
    That's not quite accurate. CDate will try and use your regional settings first, whatever they may be, then it will try alternatives if it can make a valid date. So your first example for me, with UK settings, would produce 12th May, not 5th December.
    Rory

  7. #7
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi, thank you all for your input on this.

  8. #8
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, not really, still struggling, please help. Thanks

  9. #9
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA User form - date fields convert to US format instead of UK format

    when i enter: 12/05/19 (12 May 2019), this is converted to 05/12/19 (05 December 2019).
    It doesn't clear to me, where do you enter the date (in a cell or textbox)? and where is it converted?

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by rorya View Post
    That's not quite accurate. CDate will try and use your regional settings first, whatever they may be, then it will try alternatives if it can make a valid date. So your first example for me, with UK settings, would produce 12th May, not 5th December.
    I may be guilty of implicit U.S. bias because I did not test this by changing my regional settings. However, if CDate uses regional settings first I am at a loss to explain the OP's problem:
    when i enter: 12/05/19 (12 May 2019), this is converted to 05/12/19 (05 December 2019).

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,028

    Re: VBA User form - date fields convert to US format instead of UK format

    How about
    Please Login or Register  to view this content.
    You may need to add more numbers to the part in red to reflect other text boxes.

  12. #12
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA User form - date fields convert to US format instead of UK format

    Op. Have you tried my suggestion of formatting user entry as yyyy-mm-dd (or enforce data entry in that format)?

    yyyy-mm-dd is universal date string and will work across different region settings. So when you use CDate("yyyy-mm-dd") it will work without issue and translate string to date value.
    Which then you can return to worksheet cell, and apply any format you desire.

    Alternately, another approach is to separate date field entry into Month, Day, Year. Into 3 separate textbox. Concatenate it and use CDate on the resulting string.

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by 6StringJazzer View Post
    However, if CDate uses regional settings first I am at a loss to explain the OP's problem:
    There are several places where the OP's code writes to cells without using CDate. I'd imagine that's the problem, pending clarification from the OP.

  14. #14
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Thanks CK76, formatting user entry as yyyy-mm-dd seems to work but might take time for individuals inputting data to get used to this format. Was looking for a solution (if possible) that allows users to input date as dd/mm/yy (something they are already used to and wont confuse them).

  15. #15
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA User form - date fields convert to US format instead of UK format

    As long as your users' regional settings are set to use UK format, using CDate whenever you assign a textbox's contents to a cell should be sufficient.

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Agee but I have a solution someplace have to look for it

  17. #17
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Rorya, i can confirm that my users' regional settings are set to use UK format, but for some reason (sometimes) date is converted to the American format.

  18. #18
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA User form - date fields convert to US format instead of UK format

    As I mentioned earlier, there are a few places in your code where you write textbox data to cells without wrapping it in CDate calls, at which point VBA will interpret it as a US format date if it can. You need to make sure that you use CDate every time your code does that.

  19. #19
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Thanks Rorya, i think Keebellah is looking into this for me.

  20. #20
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA User form - date fields convert to US format instead of UK format

    Could you answer my question in post 9?

  21. #21
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    You mention that the user enters a date, but in the userform he/she can double click and use the date picker.
    Does it go wrong here too?
    I explicitly added the code to set it to the correct system's date format

  22. #22
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi, text box on the user form and converted when the record is saved.

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    But still, is the user doing a double-click or just entering the date? That is the question.

  24. #24
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, yes it does go wrong even when using the date picker. NB: most times the date is converted to a US format, but sometimes its not (which is very confusing why its happening). Have a go testing the user form with different dates. for example i have just used the date picker to enter 12/05/2019 but this was converted to 05/12/2019.

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I made some minor modifications, let's see if it works first, then I'll explain
    Attached Files Attached Files

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    If you notice you'll see the dates with day less than 13 will work well, the problem will arise mostly with the dates withing the first 12 days of that month
    And that's always been a P in the A with Excel.

  27. #27
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA User form - date fields convert to US format instead of UK format

    ...the dates withing the first 12 days of that month...
    That's why I always recommend enforcing rule on data entry point. Rather than trying to interpret what the user has typed.

    This can be done using custom calendar form (date picker), using individual field for Year, Month and Day. etc.

    Then you can generate date string in "yyyy-mm-dd" format, which is universal across region settings.

  28. #28
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, Worked ok on the first 2 tests but converted the dates again on the third test

  29. #29
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I agree with CK76, it's just a question of editing the calendar form to return the correct result
    And the third test, what was the date that went wrong?
    Have maybe it's the table's date column format, check if the date format of all rows is the same for each date column

  30. #30
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, used the following dates on third test: 12/04/17, 05/12/18, 12/05/19, 12/09/2019 - all x4 dates converted on 3rd test yet used some of the same dates on 1st / 2nd test.

  31. #31
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    And are the table date columns all the same format? It could be that when a new row is added the format changes

  32. #32
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, apologies for the late reply - been away.

    When you say 'table date columns' do you mean the columns on 'MacMonitoring' sheet / tab where data is saved / appended? if so, yes - the date columns are all of the same format (i.e. *14 March 2001). Thanks

  33. #33
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    The thing is that I cannot reproduce an error here, so it's just a question of going though the vba code step by step when a value is entered to see what it does and when it changes.
    This is the worst of things, troubleshooting, and that takes time and patience, for the time being, I still don't have the answer for you

  34. #34
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    No worries Keebellah, again many thanks for your support on this. At the moment am using CK76'S suggestion of entering date as yyyy-mm-dd (which seems to work).

    As advised by CK76 earlier, any chance we can edit the custom calendar form (date picker) / code, using individual field for Year, Month and Day. etc. so that the date string is generated in "yyyy-mm-dd" format?

    Thanks

  35. #35
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA User form - date fields convert to US format instead of UK format

    any chance we can edit the custom calendar form (date picker) / code, using individual field for Year, Month and Day. etc. so that the date string is generated in "yyyy-mm-dd" format?
    You can do something this:

    Please Login or Register  to view this content.

  36. #36
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Try this and see if it works
    I modified one line in the calendar form's. tag
    Attached Files Attached Files

  37. #37
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    This is the modified code (red line)
    Please Login or Register  to view this content.
    The date will alwas be the correct serial date for the selected date, so the format will show what you want

  38. #38
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by Keebellah View Post
    This is the modified code (red line)
    Please Login or Register  to view this content.
    The date will alwas be the correct serial date for the selected date, so the format will show what you want
    Hi Keebellah, still with the modified code (red line), the date still converts to a US format.

  39. #39
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    What’s the code that processes the result into the form and then again into the worksheet?
    Number format somewhere along the way messes it up
    Could you attach your file again?

  40. #40
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, please see the re attached files as requested. Thanks
    Attached Files Attached Files

  41. #41
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    First thing I noticed is that you did NOT place the corrected code in your file.

  42. #42
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I've not made mucg progress but here is the file with some changes, still stuck and it's getting messier with the code but... just test
    Attached Files Attached Files

  43. #43
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by Keebellah View Post
    I've not made mucg progress but here is the file with some changes, still stuck and it's getting messier with the code but... just test
    Hi Keebellah, many thanks for trying to sort this. I have done a couple of tests using the attached, all seems OK apart from one date field (CT_03, DOB). Not sure why its only CT_03 now converting date yet table formatting on the 'MacMonitoring' sheet is the same. I have used: 12/04/2017 to test all date fields but CT_03 failed.

  44. #44
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Strange, they all use the same routine.
    Have you tried copying the format one one of the other date columns to the DOB column?
    DOB is also the only one being checked before saving maybe in that part of the code?

  45. #45
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Yes, i have tried copying the format. is the DOB code slightly different from the others? may be that's why?

  46. #46
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I don't know, you'll have to check the macro Check2Save in the userform

  47. #47
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by Keebellah View Post
    I don't know, you'll have to check the macro Check2Save in the userform
    I have checked, nothing stands out - its a strange one

  48. #48
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA User form - date fields convert to US format instead of UK format

    Why not just add code that formats cell range, when form entry is passed on to cell?
    Range().NumberFormat = "dd-mmm-yyyy"

    Or some such.

  49. #49
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA User form - date fields convert to US format instead of UK format

    For an example you can change your Save_New_Record sub to... (Add other date columns as needed. I only did it for first date column that I saw)
    Please Login or Register  to view this content.

  50. #50
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Thanks CK76, let me try this

  51. #51
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    You mentioned that you got an error when you implemented CK76's code, what error exactly and at which line?

  52. #52
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi given that my VB is very basic, I copied CK76’S code and pasted it into the ‘date fields’ section. (Not sure if that’s what I was supposed to do)? But the attached image is the error I got
    Attached Images Attached Images

  53. #53
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA User form - date fields convert to US format instead of UK format

    The fault appears to be in Case statement ( Case Is = 3, etc.,)

    Please Login or Register  to view this content.
    This is using code in post #42
    Last edited by torachan; 09-20-2019 at 08:22 AM.

  54. #54
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    The Ambiguous statemet ,mean that a macro with the same name is present, just delete the olde one and this error dissapears.
    That's what ambiguous means not only with programming, it means duplicate

  55. #55
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA User form - date fields convert to US format instead of UK format

    in post #42

    Please Login or Register  to view this content.
    should be

    Please Login or Register  to view this content.
    seems to make a whole lot of difference
    torachan.

  56. #56
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA User form - date fields convert to US format instead of UK format

    Code should just replace your existing one.
    Nothing was changed other than adding. DatabodyRange.Columns line

  57. #57
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Okay i went a little further. CK76 gave me the idea.
    I added a macro to set the dateformat for every date value in the edited row
    In the SAVE NEW and the SAVE MODIEFD

    Call SetTableDateNumberFormat(fnd.Row - tbl.HeaderRowRange.Row) '* the row parameter to be passed must be the actual row IN THE TABLE, NOT THE WORKSHEET

    The actual code:

    Please Login or Register  to view this content.
    There's still something wrong that the date gets turned around if I enter may 11 1999 the date is saved in the sheet as 5-Nov-1949
    Attached Files Attached Files

  58. #58
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA User form - date fields convert to US format instead of UK format

    Gentlemen.
    Rewind and merely replace "i" with "Is" and all is solved.
    torachan.

  59. #59
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Yes, i think this solves it, thanks Torachan for this.

  60. #60
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Many thanks all you for your support on this.

  61. #61
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    You see what happens when 'i' is nor what it should be thick fingers when typing, no excuse but it is
    Good to know and hope the date's format stay as they should

  62. #62
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Just check, better I use your file.

  63. #63
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, latest file is here. Many Thanks.
    Attached Files Attached Files

  64. #64
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I think that the thing is that what you see in the sheet is the format, the actual value is read intot the array and displayed on the userform, so if you change that you might get into issues when saving the date (modified) again.
    I think that requires something extra

  65. #65
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, its when I click the ‘reset form’, to search for a customer, the date records (sometimes) returned on the form (from the ‘MacMonitoring sheet) are converted even before you click the ‘Edit’ button.

  66. #66
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Read my previous post, I explained what happens you are confusing what you see with what the value actually is.
    The moment you search thé record valies are shown as they are, what you see on the sheet is thé format YOU want to see and NOT the value
    Userform TextBoxes are string values!!!
    I

  67. #67
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, i can confirm that am not confusing what i see with what the value actually is. Like i stated in my earlier post, e.g. 'Date of referral' on the 'MacMonitoring' sheet is 12 April 2017, when i do a search, this is returned as 04/12/17 on the form instead of 12/04/17. So its not the formatting am confusing.

    NB: I did say that this does not happen all the time a search is done. So when you test this, date might not be converted but does happen sometimes.

  68. #68
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Okay, clear, this something that will require that all dates be saved as serail dates and treated as such
    April 12 can therefore easily be interpreted as Dec 4
    The only dates this will not happen are dates that are clear that they cannot be turned around like sept 11 to nov 9 etc
    Like I said I’m in Macedonia and just answering on the phone
    Next week i’ll take a look but excel dates and Userform Sas in-between are a P in the A

  69. #69
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Yes, even dates like 21/08/2019 are converted to 08/21/2019 when you do a customer search. Thanks

  70. #70
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    That’s why date handling should be using serial dates

  71. #71
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Ok, thanks.

  72. #72
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    You see, I'm quite addicted to Excel LOL
    I played around with it on my little Asus transformer and see if this helps
    I also added a macro named 'set_AllDates2Serial' that does just that.
    The same code I used when saving the record as well as the modified saving
    Here's what I came up with
    Attached Files Attached Files

  73. #73
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Ok, thanks very much, let me do some testing. I will let you know how i get on. Much appreciated.

  74. #74
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, am now having issues with saving the edited / amended record. Sometimes it saves the changes, sometimes it does not save the changes. Not sure whats gone wrong?

  75. #75
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    I have noticed there's 2 'Save_Modified_Record' codes. Is this what is causing the problem?

  76. #76
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    One is not used and I forgot to remove it, isn’t it named _old?

  77. #77
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Yes, i will remove it, will that fix the issue?

  78. #78
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I cannot reproduce the issue and as far as I can see here no problems.
    I also made quite some modifications to the way the record is written but I think that there is more to gain overhauling the calendar form, I don’t quite like this one since it cannot be set to show the date you want to modify IF there is a date nut that’s not something to work on on my small asus transformeer

  79. #79
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, after a few tests, I am still getting the dates converted to a US format when I click the ‘RESET FORM’ option to search for a customer. (Please see attached snip with converted dates highlighted in yellow). Thanks
    Attached Images Attached Images

  80. #80
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    one way or another when reading thé date thé format is Rutger changed or your system’s format is US format, you’ll have to add code to force the number format at all times
    You’ ve got alk the code so I see a coding challenge for you here 😜

  81. #81
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    I can confirm that the system location is United Kingdom (please see snip).

    I might have to discard using the 'Search Customer' option / editing a record using the form. (although users found this option very user friendly rather than editing a record via the 'MacMonitoring' sheet).
    Attached Images Attached Images

  82. #82
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    You don’t need to remove that it’s just a question of modifying the code accordingly, when lgilling thé list NiX use the text instead of value when reading
    Userform fields, list contents are also text

  83. #83
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I’ll dive into it after I’m back in the Netherlands next week

  84. #84
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Thanks Keebellah, much appreciated.

  85. #85
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Well, I did take a look once in a while and got rid of some irritating things like when you already have a date and double click that it went to today's date instead of the date filled in.
    I think I did get some things running nicely so give it a try. Y use the date textbox's tag to check the value and display the date in the same format as you do on the worksheet.
    Still on vacation, but found the time to look at this. Will be back home Wednesday evening
    Attached Files Attached Files

  86. #86
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, many thanks for your support on this.

    Just tried adding first record and i got a run time error below:
    Attached Images Attached Images

  87. #87
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    One thing, did you clear all the table rows?
    In that case you will get the error
    A table must at least contain one table row at all times even if it’s an empty row.
    I did not add any code for deleting rows and there should at all times be a check that it if’s the last row with data then it only clears the contents but not remove it sk you will get the error you mentioned when requesting a databodyrange rows count

  88. #88
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA User form - date fields convert to US format instead of UK format

    This worked on file on post #85
    Please Login or Register  to view this content.

  89. #89
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    I just completed the form from the 'surname' field. i did not clear any the table rows, i did not actually even visit the 'MacMonitoring' sheet. Error comes up when you try to add new customer using the latest upload.

  90. #90
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by torachan View Post
    This worked on file on post #85
    Please Login or Register  to view this content.
    Torachan / Keebellah, should i replace it with the above code?

  91. #91
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Sorry my mistake, I corrected the code.
    Attached Files Attached Files

  92. #92
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Thanks keebellah, will let you know how it goes. Cheers

  93. #93
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Arrived today. Hope to hear (good news) from you

  94. #94
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, the other error has been sorted but it now brings another error if you try to add new customer. please see snip. Thanks
    Attached Images Attached Images

  95. #95
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Did you by any change change something in the global parameters?

    Please Login or Register  to view this content.
    No Error here

  96. #96
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, i didn't change anything but will you use the latest upload. Many Thanks.

  97. #97
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Do you mean that YOU are using the latest file I just attached ?

  98. #98
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Correct, any issues, will let you know. Cheers

  99. #99
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I know wher I goofed, I modified the obe you were using.
    Give this one a try. This the very latest where I went a little further.
    Attached Files Attached Files

  100. #100
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Thanks Keebelah, will revert

  101. #101
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah,

    I am not having any luck with these. Tried latest upload, still getting the same errors. I have also tried the upload on 08/10/2019 and got a compile error below:
    Attached Images Attached Images

  102. #102
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Sorry for that, it's strange you're running into it.
    Just delete that row, no problem (only that row)

  103. #103
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I see the problem, you are NOT using the file I sent you in the last post!!!
    the file name is MacMonitoring Latest (Keebellah)V2.3.xlsm and NOT MacMonitoring (re-upload- modif Keebellah).xlsm

  104. #104
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Hi Keebellah, just to confirm, i first downloaded and used 'MacMonitoring Latest (Keebellah)V2.3.xlsm' (it came up with the same debug error i posted on 08/10/2019). i then tried the upload previous upload i.e. 'MacMonitoring (re-upload- modif Keebellah).xlsm' to which i got a compile error. thanks

  105. #105
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    As I could see from your image the file you made the screenshot of was the other file.
    I checked and re-checked here but no errors.
    If you could explain step by step what you are doing at the moment the error appears, maybe then I can reproduce it.
    I've added, modified deleted added again, etc etc and no errors. I cannot explain what's wrong on your side.

  106. #106
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    I have re downloaded the latest upload 'MacMonitoring Latest (Keebellah)V2.3.xlsm', added surname and date of birth, hit ‘Save record’ then ‘Yes’ and this is what am getting:
    Attached Images Attached Images

  107. #107
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA User form - date fields convert to US format instead of UK format

    Works with the datepicker, faults with dd/mm/yyyy.
    torachan.

  108. #108
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    That's what I wanted to ask. how do you enter the date?
    If it's manually then it does indeed go wrong, so we'll have to figure out a way to force the datepicker.
    Than you toracha, that part was not told to me, and yes, I did not force the datepicker,
    Without ALL the exact information, no go.
    I'll see if I can alter the code so that it forces the datepicker only when editing and new, not when reading, that wss the problem we had.

  109. #109
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Now that's clear what was going wrong, typical miscommunication issue, I made some changes to the vba userform code.
    I reduced the number of code lines and put some things in one macro
    The other thing I did was include a control tip text for the date fields asking the user to NOT type the text but double-click.
    If the filed is empty (new record) er not filled in (edit record) the date field is locked for typing but it may be double-clicked
    If the field is not empty then the filed is unlocked to allow clearing the entry but still the user is asked to double-click to select a new date, this part will remain tricky for those that type the date anyway.
    I've attached the modified version 2.4

    Added a screenshot showing the controltiptext in both cases
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Keebellah; 10-15-2019 at 05:09 PM. Reason: added a screenshot image

  110. #110
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Have you had a chance to try it out again?

  111. #111
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Thanks Torachan

  112. #112
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by Keebellah View Post
    Have you had a chance to try it out again?
    Thanks Keebellah for the modified code / macro. Gonna do a few tests today and get back to you. Cheers

  113. #113
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Good luck, hope to hear (good news) from you

  114. #114
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by Keebellah View Post
    Good luck, hope to hear (good news) from you
    Hi Keebellah, been doing a few tests before getting back to you, all looking good with the date modified code / macro. However i have picked up 2 minor issues (not date related);

    1, Sometimes, keyboard types uppercase when caps lock is not on and types lowercase when caps lock is on for text boxes i.e. 'Referrer Name', 'Jobtittle', 'Notes' - (CT_17, CT_18, CT_45) etc.

    2, 'Gain £' amount figures disappears on customer search but when you click in each gain field, figures re appear but 'Total £' amount does not update.

    Apart from that, i did not have any issues with the dates.

    Many thanks for your support

  115. #115
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Good to hear the dates work.
    Point 1.: not VBA related there absolutely no code for these three fields. PC Issue (?)
    Point 2:
    The reason is becasue the data is loaded as text
    Change this one:

    In the userform macro the Function read_recordFromWorksheet

    Modify the line with the bold text, it reads .Text and make it read .Value
    Please Login or Register  to view this content.

  116. #116
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by Keebellah View Post
    Good to hear the dates work.
    Point 1.: not VBA related there absolutely no code for these three fields. PC Issue (?)
    Point 2:
    The reason is becasue the data is loaded as text
    Change this one:

    In the userform macro the Function read_recordFromWorksheet

    Modify the line with the bold text, it reads .Text and make it read .Value
    Please Login or Register  to view this content.
    Done, thanks.

    How come it is overwriting the latest added record?

  117. #117
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    My mistake (again) I think it's okay now.
    Corrected the row number check, test it please
    Attached Files Attached Files

  118. #118
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    Did you have a chance to take a look?

  119. #119
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by Keebellah View Post
    Did you have a chance to take a look?
    Sorted! Many thanks Keebellah, we are good to go.

  120. #120
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: VBA User form - date fields convert to US format instead of UK format

    I do have some suggestions, like when you enter a date of referral or a referral org, then some of the fields in that frame need to be completed to, so you should add an extra check2save for separate frames if values are entered.
    But... that's for you to practice
    Happy coding, and hope it's up to your expectations.

  121. #121
    Registered User
    Join Date
    04-24-2019
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    95

    Re: VBA User form - date fields convert to US format instead of UK format

    Quote Originally Posted by Keebellah View Post
    I do have some suggestions, like when you enter a date of referral or a referral org, then some of the fields in that frame need to be completed to, so you should add an extra check2save for separate frames if values are entered.
    But... that's for you to practice
    Happy coding, and hope it's up to your expectations.
    Good suggestion, will have a go, i hope i don't mess things up. But that's the only way to learn. Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] UK vs US Date Format in User Form
    By annieb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2013, 07:54 PM
  2. [SOLVED] Date format of yyyy-mm-dd for field entry in user form
    By ajolin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2012, 02:22 AM
  3. Set default date format of all text boxes on a user form
    By KAPearson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2012, 01:14 PM
  4. Excel User Form - Date format
    By Alice21 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-25-2012, 09:10 AM
  5. Date Format in User Form
    By Shazz in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-06-2012, 08:40 AM
  6. date format in VBA user form
    By TerryWingfield in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-15-2008, 08:31 AM
  7. Date Format problem in User Form
    By vandanavai in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-27-2006, 11:07 PM
  8. [SOLVED] date format in user form
    By TUNGANA KURMA RAJU in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2006, 01:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1