Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

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.

    VB:
    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,698

    Re: Setting The Active Control In A Form

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

    Or, why not simply
    VB:
    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,539

    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


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, 20:29
  2. Replies: 2
    Last Post: May 6th, 2008, 23:55
  3. Collect Date From User & Check If Valid
    By khenzel in forum EXCEL HELP
    Replies: 3
    Last Post: August 31st, 2007, 09:33
  4. Format Date In TextBox
    By vandanavai in forum EXCEL HELP
    Replies: 4
    Last Post: January 9th, 2007, 00:37
  5. Date format a textbox?
    By REP013 in forum EXCEL HELP
    Replies: 7
    Last Post: December 20th, 2004, 21: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