Error Handling null value field with VBA

[greneebb]

Good day,

My form has several fields, all of which require a value. But if a null value is accidentally entered into any one of the fields, an error is triggered, and the VBA page is opened at the point of the error. 

Is there a way to recover from the error seamlessly and let the user enter their intended value?

[ed57gmc · Accepted solution]

Instead of recovering, you need to validate the data first. You could use an event on each field to validate, like the OnExit event, or when they hit the Enter key, you could validate all the fields and exit the sub if it is invalid. First pop a message letting them know what field is invalid. You can even make it the active control.

[greneebb]

Thanks for the guidance. However, it would mean validating several fields. Wonder if there is one validation that could validate all the fields globally.

[ed57gmc]

All you need is something like the following for each control that is used for data input. If all the fields are the same data type, then you could create a test function and in each Exit method, pass the function the control's Value property.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   'This function tests for an empty string in a TextBox.
   'If the value is empty, then focus is not allowed to go to the next control.
If TextBox1.Value = "" Then
        MsgBox "Value can't be null. Please fix.", vbCritical, "NULL Value"
        Cancel = True
    Else
        Cancel = False
    End If
End Sub

[greneebb]

Thanks

[greneebb]

I am getting close but still getting the error message "Type miss matched": Here is the code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   'This function tests for an empty string in a TextBox.
   'If the value is empty, then focus is not allowed to go to the next control.
If TextBox1.Value = "" Then
        MsgBox "Value can't be null. Please fix.", vbCritical, "NULL Value"
        Cancel = True
    Else
        Cancel = False
    End If
End Sub




Private Sub TextBox1_Change()

Call TextBox1_Exit(0)

 
 Me.Textbox4 = Round(Me.TextBox1 * 25.4, 3)
 Me.TopPer = Round((Me.TextBox1 * 25.4) * 3.142, 3)
 
End Sub

[norman.yuan]

I assume it is this line raises the error:

Me.Textbox4 = Round(Me.TextBox1 * 25.4, 3)

Even this line

Call TextBox1_Exit(0)

indeed prevent the focus from leaving TextBox1, its value still can be empty string, thus Round(Me.TextBox1 * 25.4) would fail when the text box's value is empty, or is a non-numeric value.

Also, I do not think it is good idea to focus the textbox's value change to something else while user is typing/entering data into the text box. The user may be confused while he/she enters 1 and the textbox shows 25.400. You should do the validation with TextBox1_Exit: check the value being empty, or non-numeric value, or the numeric value may out of certain range... Only one of the cases occurs, you cancel the exit, and may want to let user know something is wrong (say, showing a message box, as you did).

[ed57gmc]

In addition to @norman.yuan comments, I would recommend using Me.Textbox1.Value to explicitly refer to the value. Secondly, you can't Round a string. Use one of the Ctype functions to convert the text to a real for example. I also think that your form probably is not clearly informing the user what to enter. If you expect someone to enter an imperial value and you are automatically converting it to metric, for example, then have a box for imperial units, and then, in the OnChange event, calculate a value for a different field that is labeled 'metric/mm/etc'.