Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Force Date Format In TextBox

  1. #1
    Join Date
    26th July 2004
    Posts
    1,319

    Force Date Format In TextBox

    Hi everybody

    Obviously there are hundreds of threads on the above subject but really i have not been able to find the correct answer and I myself have not been able to figure out the same...so thats why I am asking all you excel gurus to pl help me out

    On a user form i have a textbox for accepting date. Earlier I had linked it to calendar but now the user does not want calendar but a direct input in the textbox whereupon clicking a commandbutton the date will get supplied to another code.

    My problem is this

    1...How do I make the textbox accept date only in the "dd/mm/yyyy" format
    2....ensure that error message is displayed if there is departure from format (even though the date may otherwise be valid)
    3....supply the date in "dd/mm/yyyy" format to the subsequent code

    Thanx in advance

    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,844

    Re: Date format in text

    pangolin,

    First off letting the user type in the date string can be problematic. Hence, I believe you're better off using a date control. However, users being users, want want want. So if they want to type in the date on the exit event of the text box use the function ISDATE.


    It'll be on them to enter the MM/DD/YY correctly.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?

  3. #3
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462

    Re: Date format in text

    Hi Pangolin,

    The following code will allow the user to put in different formats, but change after input to dd/mm/yyyy. Clicking the CommandButton will put the value of the date TextBox into Cell G1 of Sheet1 in the format dd/mm/yyyy.

    VB:
    Dim dDate As Date 
    Private Sub CommandButton1_Click() 
        Sheet1.Range("G1").Value = dDate 
    End Sub 
     
    Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 
         
         'Dim dDate As Date
        dDate = DateSerial(Year(Date), Month(Date), Day(Date)) 
        TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy") 
        dDate = TextBox1.Value 
    End Sub 
    
    
    Hope this helps.

    Regards,

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th July 2004
    Posts
    1,319

    Re: Date format in text

    Hi ranger

    Thanx for ur help...the code works but if I put a date valid in mm/dd but invalid in dd/mm (for eg 18/12) it still accepts the date..in such a case I would like it to give an error message and disallow acceptance

    secondly after acceptance the value in the textbox reverts back to "mm/dd" format...can it stay in the "dd/mm" format

    thanx once again

    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462

    Re: Date format in text

    Hi Pangolin,

    Can you check in the Control Panel, that your date is not set to U.S. Format, as you seem to want European Format in your first post. The date should return to dd/mm/yyyy in the TextBox.

    I will have a look at the invalid dates problem.

    Regards,

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th July 2004
    Posts
    1,319

    Re: Date format in text

    Hi Ranger

    Yeah you very right the date was in the US format (sorry bout that- didnt check over there in the first place)

    However the problem regarding invalid date acceptance still remains...even I am working on it separately but yet unable to figure it out.....

    Anyways...thanx a million for all your help...without you even this much wouldnt have been possible

    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th July 2004
    Posts
    1,319

    Re: Date format in text

    Hi Ranger

    any luck???????

    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    26th July 2004
    Posts
    1,319

    Re: Date format in text

    Hi everybody

    well surprisingly I am not able to find a solution to my problem...

    can someone help me on this

    TIA

    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462

    Re: Date format in text

    Hi Pangolin,

    Try the following:
    VB:
    Dim dDate As Date 
    Private Sub CommandButton1_Click() 
        Sheet1.Range("G1").Value = dDate 
    End Sub 
     
    Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 
        If Mid(TextBox1.Value, 4, 2) > 12 Then 
            MsgBox "Invalid date, please re-enter", vbCritical 
            TextBox1.Value = vbNullString 
            TextBox1.SetFocus 
            Exit Sub 
        End If 
         
        dDate = DateSerial(Year(Date), Month(Date), Day(Date)) 
        TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy") 
        dDate = TextBox1.Value 
    End Sub 
    
    
    Regards,

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    26th July 2004
    Posts
    1,319

    Re: Date format in text

    Thanx Ranger

    this works perfectly

    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: May 6th, 2008, 23:55
  2. Force Date Format Entry in Text Box
    By ychadtastic in forum EXCEL HELP
    Replies: 22
    Last Post: September 5th, 2007, 14:46
  3. Force A Date Format
    By jetted in forum EXCEL HELP
    Replies: 10
    Last Post: March 20th, 2007, 03:49
  4. Format Date In TextBox
    By vandanavai in forum EXCEL HELP
    Replies: 4
    Last Post: January 9th, 2007, 00:37
  5. Format TextBox Date. Check If Date Valid
    By rsteadman in forum EXCEL HELP
    Replies: 3
    Last Post: October 25th, 2006, 01:59

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