IF statements in VBA

[VBAmalta89]

I need to create an if statement in VBA for the following problem but cant get around to understand how to actually do it:

I have three text boxes in a user form that only accept numbers. Now, I need the total of these three numbers to not exceed 100, ie a prompt is given if 100 is exceeded and the user will be unable to complete the form

how do i code this?

 

[RSXchin]

Sub its_over_9000()
    If Range("a1") + Range("a2") + Range("a3") > 100 Then
        MsgBox "TOO MUCH!!!!!"
    End If
End Sub

 

[Andrew Poulsom]

Like this?

If (Val(TextBox1.Value) + Val(TextBox2.Value) + Val(TextBox3.Value)) > 100 Then
    MsgBox "Maximum of 100 exceeded!"
    Exit Sub
End If

 

[JamesW]

Hi there,

Try:

    Dim txb As Control, temp As Single
    For Each txb In UserForm1.Controls
        If txb <> "" Then
            temp = temp + txb
        End If
    Next txb

    If temp > 100 Then
        MsgBox "Value is over 100: " & temp
        TextBox1.SetFocus
    Else
        doStuff
    End If

This will not count blank textboxes.

 

[RSXchin]

Mine was more fun :P

Why do you need exit sub? (honest question)

 

[VBAmalta89]

thanks a real lot for your replies. I managed to do it now

 

[Andrew Poulsom]

Mine was more fun :P

Why do you need exit sub? (honest question)

Presumable you wouldn't want to continue processing if the maximum was exceeded.

 

[RSXchin]

Thanks Andrew. Good call

 

[JamesW]

Thanks Andrew. Good call

And he is using textboxes, not ranges

 

[RSXchin]

Well, I assumed he knew to change that to what he needed. Silly me.

actually, now that I think about it
"For Each txb In UserForm1.Controls"

How do you know it's called UserForm1? and how do you know there aren't other controls than the 3 he's adding?

I'm just giving you a hard time.