Calculating multiple text boxes on a user form

[kevinc1973]

Hello all,
I am calculating multiple text boxes on a user form and the code i am using seems to be working out okay. The problem i'm having is if I tab into the text boxes that have the code to multiply and divide are left blank are giving me a error message. Some days I may leave those text boxes blank because of no data. How do I go around this so those text boxes stay blank.

Private Sub textbox4_enter()
TextBox4.value = (TextBox2.value - TextBox3.value)
End Sub

Private Sub TextBox8_Enter()
TextBox8.value = (TextBox2.value - TextBox7.value)
End Sub

Private Sub TextBox9_Enter()
TextBox9.value = ((TextBox8.value * 100)) / (TextBox4.value)
End Sub]

If I don't enter into the text boxes it works but i can't guarantee no one else will not tab into the text boxes and get the error message, as there will be multiple people using the user form. thanks for any help

 

[Yongle]

One method
- prevent calculation if either textbox is empty

Use se something like this as first line of TextBox9_Enter

If Len(TextBox4.Text) = 0 Or Len(TextBox8.Text) = 0 Then Exit Sub

 

[dmt32]

try following update to your codes

Private Sub textbox4_enter()
    TextBox4.Value = (Val(TextBox2.Value)) - Val(TextBox3.Value)
End Sub


Private Sub TextBox8_Enter()
    TextBox8.Value = (Val(TextBox2.Value)) - Val(TextBox7.Value)
End Sub


Private Sub TextBox9_Enter()
    TextBox9.Value = (Val(TextBox8.Value) * 100) / Val(TextBox4.Value)
End Sub

The VAL function stops reading a string at the first character that it can't recognize as part of a number so should return 0 when you TAB around empty textboxes

Dave

 

[jmacleary]

try following update to your codes

Private Sub textbox4_enter()
    TextBox4.Value = (Val(TextBox2.Value)) - Val(TextBox3.Value)
End Sub


Private Sub TextBox8_Enter()
    TextBox8.Value = (Val(TextBox2.Value)) - Val(TextBox7.Value)
End Sub


Private Sub TextBox9_Enter()
    TextBox9.Value = (Val(TextBox8.Value) * 100) / Val(TextBox4.Value)
End Sub

The VAL function stops reading a string at the first character that it can't recognize as part of a number so should return 0 when you TAB around empty textboxes

Dave

This solution will still error if TextBox9 is entered when TextBox4 is empty (you would get a division by zero error).

So, for TextBox9 do something like:

Private Sub TextBox9_Enter()
    if Val(TextBox4.Value)<>0 then
       TextBox9.Value = (Val(TextBox8.Value) * 100) / Val(TextBox4.Value)
    endif
End Sub

 

[dmt32]

This solution will still error if TextBox9 is entered when TextBox4 is empty (you would get a division by zero error).

So, for TextBox9 do something like:

Private Sub TextBox9_Enter()
    if Val(TextBox4.Value)<>0 then
       TextBox9.Value = (Val(TextBox8.Value) * 100) / Val(TextBox4.Value)
    endif
End Sub

Silly me - one of my senior moments

Dave

 

[kevinc1973]

Thanks everyone,
I will try the codes when I'm back at work. I also tried

Textbox9.dblclick

That let me tab through with no data entered I just had to double click in the box to have it calculate.

 

[kevinc1973]

Thank you guys for the help it worked perfectly just by entering the Val in the code. Once again you guys are awesome thank you
Kevin