setfocus problem on userform

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why not just use this?
Code:
Cancel =True
 

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
help me understand

Norrie,

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 24, 2006
Messages
68

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

this should be your code, cleaned up :)
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
forgot to tell you that your code will not see mistakes like
789456
you would better use code like
Code:
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

Board Regular
Joined
May 24, 2006
Messages
68
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.
 

Forum statistics

Threads
1,137,337
Messages
5,680,900
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top