VBA
Discuss AutoCAD ActiveX and VBA (Visual Basic for Applications) questions here.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Error Handling null value field with VBA

7 REPLIES 7
SOLVED
Reply
Message 1 of 8
greneebb
289 Views, 7 Replies

Error Handling null value field with VBA

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?

 

 

7 REPLIES 7
Message 2 of 8
ed57gmc
in reply to: greneebb

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.

Ed


Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
How to post your code.

EESignature

Message 3 of 8
greneebb
in reply to: ed57gmc

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

Message 4 of 8
ed57gmc
in reply to: greneebb

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

 

Ed


Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
How to post your code.

EESignature

Message 5 of 8
greneebb
in reply to: greneebb

Thanks

Message 6 of 8
greneebb
in reply to: ed57gmc

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
Message 7 of 8
norman.yuan
in reply to: greneebb

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

 

 

 

Norman Yuan

Drive CAD With Code

EESignature

Message 8 of 8
ed57gmc
in reply to: norman.yuan

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

Ed


Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
How to post your code.

EESignature

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

AutoCAD Inside the Factory


Autodesk Design & Make Report