topic Re: Error Handling null value field with VBA in VBA Forum

Error Handling null value field with VBA

greneebb — Wed, 27 Nov 2024 01:52:52 GMT

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?

Re: Error Handling null value field with VBA

Ed__Jobe — Thu, 28 Nov 2024 02:39:21 GMT

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.

Re: Error Handling null value field with VBA

greneebb — Sun, 01 Dec 2024 10:10:50 GMT

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

Re: Error Handling null value field with VBA

Ed__Jobe — Mon, 02 Dec 2024 21:56:24 GMT

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.

Re: Error Handling null value field with VBA

greneebb — Mon, 09 Dec 2024 10:26:03 GMT

Thanks

Re: Error Handling null value field with VBA

greneebb — Mon, 09 Dec 2024 10:43:59 GMT

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

Re: Error Handling null value field with VBA

Norman_Yuan — Mon, 09 Dec 2024 19:46:51 GMT

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).

Re: Error Handling null value field with VBA

Ed__Jobe — Wed, 11 Dec 2024 04:28:23 GMT

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'.