Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Format TextBox Date. Check If Date Valid

  1. #1
    Join Date
    8th August 2006
    Posts
    19

    Format TextBox Date. Check If Date Valid

    I have a small subroutine that checks an textbox entered by the user to make sure the data is entered in the correct format. I used the afterupdate event, so when the user tabs or clicks out this sub runs. The problem I'm having with this sub is that I want the textbox that was entered incorrectly to become active again instead of the box that is next in the tab order or the box that the user clicks. I used the SetFocus property to try to do this but it has not bee working for me.

    Code:
    Private Sub TAdvDate_AfterUpdate()
    datevaluechecker = TAdvDate.Value
    dcheck1 = Left(datevaluechecker, 2)
    dcheck2 = Mid(datevaluechecker, 3, 1)
    dcheck3 = Mid(datevaluechecker, 4, 2)
    dcheck4 = Mid(datevaluechecker, 6, 1)
    dcheck5 = Mid(datevaluechecker, 7, 2)
    If datevaluechecker = "" Then
    Else
        If dcheck1 = 1 Or dcheck1 = 2 Or dcheck1 = 3 Or dcheck1 = 4 _
            Or dcheck1 = 5 Or dcheck1 = 6 Or dcheck1 = 7 Or dcheck1 = 8 _
            Or dcheck1 = 9 Or dcheck1 = 10 Or dcheck1 = 11 Or dcheck1 = 12 Then
                If dcheck2 = "/" And dcheck4 = "/" Then
                Else
                    dateerror = MsgBox("The date you entered was not in the correct format. Please re-enter using mm/dd/yy.", 0, "Error!")
                    TAdvDate.SetFocus
            End If
        End If
    End If
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,785

    Re: Setting The Active Control In A Form

    Use the Exit Event instead and set the Cancel parameter to False.

    Or, why not simply
    Code:
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If TextBox1 = vbNullString Then Exit Sub
        
            If IsDate(TextBox1) Then
              TextBox1 = Format(TextBox1, "Short Date")
            Else
              MsgBox "Non valid date"
              Cancel = True
            End If
    End Sub

  3. #3
    Join Date
    8th August 2006
    Posts
    19

    Re: Format TextBox Date. Check If Date Valid

    Hey thanks that worked, I had tried to use the exit case before, but didn't think to change the cancel value with the if statement.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,542

    Re: Format TextBox Date. Check If Date Valid

    Why don't you use IsDate to check if the user enters a valid date?

    You could then convert it to your required format.

    That should give the user a little flexibility.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th January 2015
    Posts
    29

    Re: Format TextBox Date. Check If Date Valid

    I see this is a old thread but after searching I found this code to work best for me.

    Code:
    Private Sub LogInDOITextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        
    'Test if Date value
        If IsDate(LogInDOITextBox.Value) = False Or Len(LogInDOITextBox.Value) < 10 Then
            MsgBox ("Must enter a valid date being mm/dd/yyyy")
            LogInDOITextBox.Value = Null
            Cancel = True
        End If
        
    End Sub
    Hope this helps others
    Last edited by Fuzz-Head; January 15th, 2015 at 06:13. Reason: Forgot to add the Cancel = True

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 9
    Last Post: January 6th, 2014, 21:29
  2. Replies: 2
    Last Post: May 7th, 2008, 00:55
  3. Collect Date From User & Check If Valid
    By khenzel in forum EXCEL HELP
    Replies: 3
    Last Post: August 31st, 2007, 10:33
  4. Format Date In TextBox
    By vandanavai in forum EXCEL HELP
    Replies: 4
    Last Post: January 9th, 2007, 01:37
  5. Date format a textbox?
    By REP013 in forum EXCEL HELP
    Replies: 7
    Last Post: December 20th, 2004, 22:43

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