Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Setting Date Format for a Text Box in a User Form

  1. #1
    Board Regular
    Join Date
    Dec 2009
    Location
    Wetherby, England
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Setting Date Format for a Text Box in a User Form

    Hi All,

    I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.

    It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.

    Please can someone give me a bit of guidence or link me to a good rescource if missed it in the search.

    Thanks in advance.

    G

  2. #2
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    I would either add the calendar control, or use a spinner for year, month and day. Textbox input isn't worth the bother.

  3. #3
    Board Regular id107's Avatar
    Join Date
    Apr 2008
    Location
    Aberdeen, UK
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    Code:
            If Not IsDate(TextBox1) Then
                MsgBox "Please input a valid date"
                Exit Sub
            Else
                YourVariable = Format(TextBox1, "dd/mm/yyyy")
            End If
    Edit: xld's suggestion is much better
    Last edited by id107; Dec 15th, 2009 at 10:35 AM.

  4. #4
    Board Regular
    Join Date
    Dec 2009
    Location
    Wetherby, England
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    Quote Originally Posted by xld View Post
    I would either add the calendar control, or use a spinner for year, month and day. Textbox input isn't worth the bother.
    Quote Originally Posted by id107 View Post
    Code:
            If Not IsDate(TextBox1) Then
                MsgBox "Please input a valid date"
                Exit Sub
            Else
                YourVariable = Format(TextBox1, "dd/mmm/yyyy")
            End If
    xld's suggestion is much better
    Yeah I thought I had it nailed with the calendar control but when I distributed it to some staff to use half of them got the below message whenenver the user form was openend.

    "Could not load an object because it is not available on this system"

    I'm going to try and work on the macro above.

    Thanks

  5. #5
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    Quote Originally Posted by garbfink View Post
    Yeah I thought I had it nailed with the calendar control but when I distributed it to some staff to use half of them got the below message whenenver the user form was openend.

    "Could not load an object because it is not available on this system"

    I'm going to try and work on the macro above.

    Thanks
    You could sitribute the calendar control as well, or just use pinners as I suggested.

  6. #6
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    The problem with the other approach is that it doesn't ensure a valid date is input, you should really check it as they type, and that is a real faff.

  7. #7
    Board Regular id107's Avatar
    Join Date
    Apr 2008
    Location
    Aberdeen, UK
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    Yup. I've just tested the code I posted and if it's before the 12th of the month; it flips the day and the month in the output. Lots of faff required to get it working properly.

  8. #8
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    Quote Originally Posted by id107 View Post
    Yup. I've just tested the code I posted and if it's before the 12th of the month; it flips the day and the month in the output. Lots of faff required to get it working properly.
    That should easily be remedied with

    Code:
            If Not IsDate(TextBox1) Then
                MsgBox "Please input a valid date"
                Exit Sub
            Else
                YourVariable = Format(CDate(TextBox1.Text), "dd/mm/yyyy")
            End If
    but it still doesn't validate as input

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    I agree with Bob (xld) that there are better ways than a TextBox, but here's a mask if you want one:

    http://www.mrexcel.com/forum/showthread.php?p=1557042
    Microsoft MVP - Excel

  10. #10
    Board Regular
    Join Date
    Dec 2009
    Location
    Wetherby, England
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Setting Date Format for a Text Box in a User Form

    Quote Originally Posted by xld View Post
    You could sitribute the calendar control as well, or just use pinners as I suggested.
    how would i distibute this control?

    I've been messing around with the vba and am having the day / month juggling issue even with xld's revised code.

    I'm either going to have it as just a straight text box, or implement the spinners.

    I can't believe that setiing a format on a text box is so annoying!

    Thanks for the help.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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