Set Focus Not Working on MsgBox Response

[reberryjr]

I'm using this code to evaluate the value of a textbox on a UF. If the value is invalid, a msgbox is triggered. Once the EU clicks the OK button, I want the focus back on the offending textbox. Instead, the focus is moving to the next textbox, which also has similar validation occurring, so when the EU goes back to correct the offending value, they get an error for exiting the textbox they just left.

I've researched this, and every post I've seen has txtbox.setfocus, which I have; so I'm not sure why it's not working. In theory, I could put this code in a different spot, so that it's checked after the EU clicks a cmd button, but I've yet to find a way to validate that a textbox value is ##/##/##. ISDATE doesn't work, so I'm having to evaluate the contents twice.

Thoughts?

[Private Sub txt_DoB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Validates that the field value is a date, and converts it to MM/DD/YY format. _
If the field value is not a date, an error is thrown.
Dim Response As VbMsgBoxResult
Dim IsOk As Boolean
    IsOk = True
If IsDate(Me.txt_DoB.Value) Then
    Me.txt_DoB = Format(Me.txt_DoB.Value, "MM/DD/YY")
Else
    MsgBox "Please enter a valid DoB, in MM/DD/YY format."
    If Response = vbOK Then Me.txt_DoB.SetFocus
    Exit Sub
    IsOk = False
End If
End Sub/CODE]

 

[Rick Rothstein]

I'm using this code to evaluate the value of a textbox on a UF. If the value is invalid, a msgbox is triggered. Once the EU clicks the OK button, I want the focus back on the offending textbox. Instead, the focus is moving to the next textbox, which also has similar validation occurring, so when the EU goes back to correct the offending value, they get an error for exiting the textbox they just left.

I've researched this, and every post I've seen has txtbox.setfocus, which I have; so I'm not sure why it's not working. In theory, I could put this code in a different spot, so that it's checked after the EU clicks a cmd button, but I've yet to find a way to validate that a textbox value is ##/##/##. ISDATE doesn't work, so I'm having to evaluate the contents twice.

Thoughts?

[Private Sub txt_DoB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Validates that the field value is a date, and converts it to MM/DD/YY format. _
If the field value is not a date, an error is thrown.
Dim Response As VbMsgBoxResult
Dim IsOk As Boolean
    IsOk = True
If IsDate(Me.txt_DoB.Value) Then
    Me.txt_DoB = Format(Me.txt_DoB.Value, "MM/DD/YY")
Else
    MsgBox "Please enter a valid DoB, in MM/DD/YY format."
    If Response = vbOK Then Me.txt_DoB.SetFocus
    Exit Sub
    IsOk = False
End If
End Sub/CODE][/QUOTE]

To stop the code from moving on, set the [COLOR="#0000FF"]Cancel[/COLOR] argument to [COLOR="#0000FF"]True[/COLOR]...

[COLOR="#0000FF"]Cancel = True[/COLOR]

Also, I do not think you will need that [COLOR="#0000FF"]Exit Sub[/COLOR] after you do that.

 

[reberryjr]

Thank you @Rick Rothstein !!! This works now!

Private Sub txt_DoB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Validates that the field value is a date, and converts it to MM/DD/YY format. _
If the field value is not a date, an error is thrown.
Dim Response As VbMsgBoxResult
Dim IsOk As Boolean
    IsOk = True
If IsDate(Me.txt_DoB.Value) Then
    Me.txt_DoB = Format(Me.txt_DoB.Value, "MM/DD/YY")
Else
    MsgBox "Please enter a valid DoB, in MM/DD/YY format."
    If Response = vbOK Then Me.txt_DoB.SetFocus
    Cancel = True
End If
End Sub

 

[reberryjr]

@rickrothstein , so I've moved on to validating data after a cmd button click, but before data moves to worksheets. The SetFocus does not appear to be working again. I've tried it with and without the Cancel = True, with and without the IsOK = True. What am I missing?

Private Sub cmd_Submit_Click()

Dim cGender, cEntryType, cPymtFreq, cPymtStatus, cStatus, cPymtMethod, cRefCat, cFacilitator, cPymtReason, cBiosClientID, cBiosNickname, cStatsClientID As Range
Dim ws1, ws2, ws3, ws4, ws5, ws6, ws7 As Worksheet


Dim Response As VbMsgBoxResult
Dim IsOk As Boolean
    IsOk = True


Set ws1 = ThisWorkbook.Sheets("Management")
Set ws2 = ThisWorkbook.Sheets("Summaries")
Set ws3 = ThisWorkbook.Sheets("Bios")
Set ws4 = ThisWorkbook.Sheets("Stats")
Set ws5 = ThisWorkbook.Sheets("Pymt Tracker")
Set ws6 = ThisWorkbook.Sheets("Financials")
Set ws7 = ThisWorkbook.Sheets("Variables")


LastRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row
LastRow3 = ws3.Range("E" & Rows.Count).End(xlUp).Row
LastRow4 = ws4.Range("C" & Rows.Count).End(xlUp).Row
LastRow5 = ws5.Range("C" & Rows.Count).End(xlUp).Row
LastRow6 = ws6.Range("D" & Rows.Count).End(xlUp).Row
LastRow7 = ws7.Range("J" & Rows.Count).End(xlUp).Row


If Len(Me.txt_First.Value) = 0 Then
    MsgBox "Please enter a First Name for the Client."
    If Response = vbOK Then Me.txt_First.SetFocus
    'Cancel = True
End If