<Resolved> How to stop error message if cancel pressed

[Borg2of6]

I am using the code below to validate the entry in a text field:

Private Sub txtHomeNotes_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim RespMsg
If Len(txtHomeNotes.Value) > 150 Then
RespMsg = MsgBox("Notes must not be more than 150 characters in length." & Chr(10) & "Please compress message", vbExclamation + vbOKOnly, "Notes Error")
txtHomeNotes.SetFocus
End If
End Sub

If the user has entered 151 or more characters in the notes box and then presses the cancel command button, the MsgBox is still displayed. Is there any way to detect that the Cancel command button has been pressed, therefore allowing me to supress the error message.

I would prefer all validation to be done on exit from text box rather than upon pressing the OK command button, as I am using validation for several other text boxes as well. They all give the same problem.

I am using VBA for Excel.

[si_the_geek]

Hi Borg2of6, welcome to VBForums!

I'll start with mentioning a couple of improvements to your current code. The first is that you don't need to set up a variable to use a messagebox, you just need to remove the brackets.

The other is that you shouldn't use setfocus, as the other control (that the user was about to go to) may get the Enter and Exit events (which can create an endless loop if the other control has the same problem). Instead you should just set the Cancel parameter to true, which has the benefits of SetFocus but doesn't fire the other events.

Private Sub txtHomeNotes_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 
 If Len(TextBox3.Value) > 5 Then
  MsgBox "Notes must not be more than 150 characters in length." & Chr(10) & "Please compress message", vbExclamation + vbOKOnly, "Notes Error"
  Cancel = True
 End If
 
End Sub

Unfortunately I don't know how you can tell if the Cancel button has been pressed at this point, as this event is fired before the next control gets the focus. (you can tell the control that currently has focus by using Me.ActiveControl.Name ).

[Borg2of6]

Thank you for your reply. I have implemented the suggested improvements. I will remeber the Cancel for next time as well.

Anybody else out there know how I can resolve the original problem?

[Borg2of6]

Since I posted the problem I have continued to research this problem on the internet. I have now found a solution on another help forum. The solution incorporated into my code is: (amendments in blue)

[COLOR=Blue]Dim bCancel as Boolean[/COLOR]
 
Private Sub txtHomeNotes_Exit(ByVal Cancel As MSForms.ReturnBoolean)
[COLOR=Blue]If bCancel = False Then[/COLOR]
    If Len(txtHomeNotes.Value) > 250 Then
        MsgBox "Please enter a maximum of 250 characters in the notes box", vbExclamation + vbOKOnly, "Notes Error"
        Cancel = True
    End If
[COLOR=Blue]End If[/COLOR]
End Sub
 
Private Sub cmdCancel_Click()
[COLOR=Blue]bCancel = True[/COLOR]
Unload Me
End Sub

It appears that the click event on the Cancel button is processed before the txtAdjust exit event.