Return to original text box in case of an error

[Giordano Bruno]

I have a userform with 9 controls, of which 6 are textboxes. Some errors can be detected only on leaving the textbox. If an illegal entry is detected on leaving a textbox, my _AfterUpdate code will erase the entry and give a warning message. Typically users will have tabbed to the next textbox, but they may click any other control. I would like thecursor to be forced back to the empty textbox so that they may correct the error. Effectively, whatever control they then select, they will be returned to the textbox where the error was detected. If this is not possible, that information will stop me wasting any more time.

 

[Legacy 143009]

Hi, This is a very small example to get you the concept:

Dim myTextBox As Object
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Set myTextBox = ActiveControl
End Sub
Private Sub TextBox1_Change()
  If "some_error" Then
    myTextBox.SetFocus
  End If
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Set myTextBox = ActiveControl
End Sub
Private Sub TextBox2_Change()
  If "some_error" Then
    myTextBox.SetFocus
  End If
End Sub

 

[Giordano Bruno]

Thanks for that Flashbond. A problem I see is that "If some error" is in the TextBox1_Change code. I have various tests for illegal entries in the TextBox, e.g. letters entered instead of numbers. The problem occurs when someone tabs off the TextBox. It is only when that that happens, that I am able to test the final value that is proposed and determine whether or not it is 'legal'. Somehow I need to cancel the change to a new textbox and return the focus to the previous TextBox. As an example, if values must be greater than 10, "2" is not an error as it may be the start of "20". If the TextBox is left at that stage, "2" is an error. I wish to be able to return the user to that text box with a message explaining the error. The only problem I have is cancelling the selection change.

 

[Legacy 143009]

It was an interesting exercise for me This is the cleanest solution I could come up with:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Cancel = makeChecks
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Cancel = makeChecks
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Cancel = makeChecks
End Sub
Function makeChecks() As Boolean
  Select Case ActiveControl.Name
  Case "TextBox1"
  If CDbl(ActiveControl.Text) > 10 Then
    MsgBox "TextBox1 must be smaller than 10"
    makeChecks = True
  End If
  Case "TextBox2"
  If CDbl(ActiveControl.Text) < 10 Then
    MsgBox "TextBox2 must be greater than 10"
    makeChecks = True
  End If
  Case "TextBox3"
  If CDbl(ActiveControl.Text) <> 10 Then
    MsgBox "TextBox2 must be equal than 10"
    makeChecks = True
  End If
  End Select
End Function

 

[Giordano Bruno]

My most grateful thanks Flashbond. It works perfectly and I am greatly in debt to you. I'd been working for two days with no success to get what you've given me.
Not that it matters in the least, but your last message should be "equal to 10"
Thanks again for all the work you've put into this. It is much appreciated.

 

[Legacy 143009]

I am glad it did work!

Not that it matters in the least, but your last message should be "equal to 10"

Oh yes, it should be TextBox3 also. I was just quick typing

Thanks for the feedback