Add an if statement to a userform?

[DipDip]

Hi Peeps,
I need some help please. I have created an excel sheet with userforms in it. One of the userforms, asks for 6 sets of numbers.

Total Hours
Physiotherapy
Occupational Therapy
SALT
Dietetics
Psychology

It's used to track therapy costs.

What I wanted to do was that if the total of the bottom 5 text boxes didn't equal the Total hours entry, then it would display an error, so that they would make sure that it was correct before if carries on with the rest of the coding to enter the data into an excel sheet. Sometimes only one to two boxes would be filled, as you wouldn't get someone having all the different types of therapy in the same session/day.

Any help of how I would go around doing this? I have trawled the net and can't seem to figure it out. I have managed to learn how to create userforms and copy the information into specific sheets and run macros etc. (wow they are so good wish I had learnt about them earlier)

Cheers in advance peeps.

 

[Fluff]

How about something like

Private Sub CommandButton1_Click()
    Dim TotHrs As Double
    
    TotHrs = CDbl([COLOR=#ff0000]textbox2[/COLOR].Value) + CDbl([COLOR=#ff0000]textbox3[/COLOR].Value)
    If TotHrs < CDbl([COLOR=#ff0000]textbox1[/COLOR].Value) Then
        MsgBox "Incorrect hours"
        Exit Sub
    End If

End Sub

Changing textbox names to suit & adding in the other 3 textboxes to sum

 

[DipDip]

Cheers for that. It partially works for what I need. I have tested it and it only works if every textbox has a value. So I had to type in 0 in every empty box to make it work. Is there a way of making it work without having the need to type in a 0 every time there is nothing to declare? Or maybe I am missing something?

 

[Fluff]

Somewhat messy, but can't think of a "cleaner" way.

    Dim TotHrs As Double
    
    If Len(textbox2.Value) > 0 Then TotHrs = TotHrs + CDbl(textbox2.Value)
    If Len(textbox3.Value) > 0 Then TotHrs = TotHrs + CDbl(textbox3.Value)
    
    If TotHrs < CDbl(TextBox1.Value) Then
        MsgBox "Incorrect hours"
        Exit Sub
    End If
    MsgBox TotHrs

Adding in the remaining tbx to sum

 

[DipDip]

Sorry but I'm having trouble trying to figure out how that will fit with my code. I have attached it below if you could help me out.

Dim TotHrs As Double
    
    TotHrs = CDbl(CONPhysioBox.Value) + CDbl(CONOccBox.Value) + CDbl(CONSALTBox.Value) + CDbl(CONDietBox.Value) + CDbl(CONPsychBox.Value)
    If TotHrs < CDbl(TotalBox.Value) Then
        MsgBox "Incorrect hours, please make sure that they add up"
        Exit Sub
    End If

From what you have written, and the way I am reading it, it only works if one text box is less than 0.

 

[Fluff]

Remove this line

    TotHrs = CDbl(CONPhysioBox.Value) + CDbl(CONOccBox.Value) + CDbl(CONSALTBox.Value) + CDbl(CONDietBox.Value) + CDbl(CONPsychBox.Value)

and replace it with

    If Len(CONPhysioBox.Value) > 0 Then TotHrs = TotHrs + CDbl(CONPhysioBox.Value)
    If Len(CONOccBox.Value) > 0 Then TotHrs = TotHrs + CDbl(CONOccBox.Value)

You'll need to add 3 more lines in the same style, for the other 3 textboxes.

Because textbox values are text, in order to add them up they need to be converted into numerical values, which is what CDbl does. However is a textbox is blank, you get an error as there is nothing to convert.
This code checks to see if the length of the textbox value is >0 (if the box is empty the length=0). Then if the length is greater than 0, it converts the contents into a number & adds it to the total hours.

 

[DipDip]

Oh right!!! I thought that the > 0 bit was if the value was over 0. Lol

 

[DipDip]

Amazing!!! That's worked a treat! Thank you so so much!!!

 

[Fluff]

Glad to help & thanks for the feedback