setfocus problem on userform

[Smithgall]

i have a text box that allows a user to enter a date. I want them to enter it as 041670 I then handle the input according and print out April 16, 1970.

However is they enter something else such as 04161970 i get an errror. To combat that i have the following code.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox3.Value Like "[0-9][0-9][0-9][0-9][0-9][0-9]" Then
TextBox3 = Format(TextBox3.Value, "00-00-00")
TextBox3.Value = Format(TextBox3.Value, "mmmm dd, yyyy")
Else
MsgBox "Date must be entered as MMDDYY", vbOKOnly + bCritical
Me.TextBox3 = ""
Me.TextBox3.SetFocus

End If
End Sub

it works great EXCEPT the setfocus does not work. after the msgbox is clicked textbox3 does clear but the focus go to the next textbox. I thought the code Me.TextBox3.SetFocus was fairly straightforward but doesnt seem to work. BTW i originally did it with the ME but added it in an attempt to figure it out so i dont thing that is problem.

 

[Norie]

Why not just use this?

Cancel =True

 

[Smithgall]

help me understand

Norrie,

how would i use the cancel = true that you mention?

 

[Norie]

Just put it in the code.

If you look at the sub's header you'll see that it passes a parameter Cancel.

If you set that to true within the code then the Exit event should be cancelled.

 

[Smithgall]

Solved. thanks so much

Norrie, thanks so much that was obvious once you pointed it out. It solved the problem very simply.

 

[erik.van.geit]

Hi,

this should be your code, cleaned up

Private Sub TextBox3_Exit(ByVal Cancel As msforms.ReturnBoolean)

    With TextBox3
        If .Value Like "[0-9][0-9][0-9][0-9][0-9][0-9]" Then
        .Value = Format(.Value, "00-00-00")
        .Value = Format(.Value, "mmmm dd, yyyy")
        Else
        MsgBox "Date must be entered as MMDDYY", vbOKOnly + vbCritical
        .Value = ""
        Cancel = True
        End If
    End With

End Sub

potential problem
when clicking (accidently) again in the textbox, even without changing anything, you will get the popup and your box will be cleared again
There is somehow a lack of logic: if you force the user to use a certain format, don't change it yourself !
possible solutions
1. use a label to display the formatted month (would not be my favorite approach)
2. use the needed format when exiting the userform
3. change your code to allow the other syntax (more complicated and still quite illogic)
4. disable the textbox when a valid entry is made (not very beautiful and problematic if the entry appears to be wrong)
There might be other solutions ...
I would definitely go for option 2.

TIP
in the controltiptext (see properties) you can write some info how to fill in the box

kind regards,
Erik

 

[erik.van.geit]

forgot to tell you that your code will not see mistakes like
789456
you would better use code like

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With CreateObject("vbscript.RegExp")
    .Pattern = "^(\d{1,2}/){2}(\d{2}|\d{4})" ' d(m)/m(d)/yy or d(m)/m(d)/yyyy
    If .test(TextBox1.Text) And IsDate(TextBox1.Text) Then
        Exit Sub
    Else
        MsgBox "Invalid Entry"
        TextBox1.Text = ""
        Cancel = True
    End If
End With
End Sub

this will only allow dates like 24/12/2006
(I'm not an expert concerning this Pattern-code)

arrgh! dates in VBA
more info in this difficult thread
http://www.mrexcel.com/board2/viewtopic.php?t=155199

I'm always using three boxes (+ 3 labels) to let the user fill in dates: so I'm sure for 100% where I pick my day - month - year

best regards,
Erik

 

[Smithgall]

thanks eric

Eric, thanks for the information. i will have to study your code. iamlearning as i go here so thanks for giving me ways to clean up the code. i do tend to create more complexities than needed. i will try your code and see what happens. Thanks again.