Total text boxes to equal 100

[cschoyer]

Hi all,
I have a userform that has 5 text boxes and 5 list boxes. The user enters in percentages in the text boxes, and these percentages must total to 100% (even if they only use 2 text boxes and the remaining three are blank).

Private Sub EnterButton_Click()

Dim AllocationTotal As Double
Dim i As Long

For i = 1 To 5
    AllocationTotal = AllocationTotal + Val(Userform1.Controls("Allocation1" & i).Text)
Next i
If Total <> 100 Then
    MsgBox "Allocation entries must total to 100."
    Exit Sub
End If

End Sub

Right now I am getting a run-time error 'could not find the specified object. I'm stumped on how to fix this! Any help is much appreciated!

[shg]

What do you suppose that error means?

What are the names of the controls?

[cschoyer]

IT highlights the line <AllocationTotal = AllocationTotal + Val(Userform1.Controls("Allocation1" & i).Text)> when the run-time error comes up. I am not sure what the controls are as I got this code from another forum.

[Pulsus]

cschoyer,

I know it´s not a perfect code, but works here:

Private Sub EnterButton_Click()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer

If TextBox1.Value <> "" Then
A = TextBox1.Value
End If

If TextBox2.Value <> "" Then
B = TextBox2.Value
End If

If TextBox3.Value <> "" Then
C = TextBox3.Value
End If

If TextBox4.Value <> "" Then
D = TextBox4.Value
End If

If TextBox5.Value <> "" Then
E = TextBox5.Value
End If

If A + B + C + D + E <> 100 Then
    MsgBox "Allocation entries must total to 100."
Else
MsgBox "The sum are 100 Now."
    Exit Sub
End If

End Sub

[cschoyer]

Thanks Pulsus, however I am getting a type-mismatch error. I know it is probably because my text box are formatted as text and not numbers, but I'm not sure how to switch it!

Private Sub Allocation1_BeforeUpdate(ByVal cancel As MSForms.ReturnBoolean)
    If Allocation1.Text <> "" Then
        Me.Allocation1.Value = Format(Me.Allocation1 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub

Private Sub Allocation2_BeforeUpdate(ByVal cancel As MSForms.ReturnBoolean)
    If Allocation2.Text <> "" Then
        Me.Allocation2.Value = Format(Me.Allocation2 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub

Private Sub Allocation3_BeforeUpdate(ByVal cancel As MSForms.ReturnBoolean)
    If Allocation3.Text <> "" Then
        Me.Allocation3.Value = Format(Me.Allocation3 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub

Private Sub Allocation4_BeforeUpdate(ByVal cancel As MSForms.ReturnBoolean)
    If Allocation4.Text <> "" Then
        Me.Allocation4.Value = Format(Me.Allocation4 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub

Private Sub Allocation5_BeforeUpdate(ByVal cancel As MSForms.ReturnBoolean)
    If Allocation5.Text <> "" Then
        Me.Allocation5.Value = Format(Me.Allocation5 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub
Private Sub EnterButton_Click()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer

If Allocation1.Value <> "" Then
A = Allocation1.Value
End If

If Allocation2.Value <> "" Then
B = Allocation2.Value
End If

If Allocation3.Value <> "" Then
C = Allocation3.Value
End If

If Allocation4.Value <> "" Then
D = Allocation4.Value
End If

If Allocation5.Value <> "" Then
E = Allocation5.Value
End If

If A + B + C + D + E <> 100 Then
    MsgBox "Allocation entries must total to 100."
    Exit Sub
End If

[Pulsus]

cschoyer,

Try This:

Private Sub EnterButton_Click()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer

If TextBox1.Value <> "" Then
A = Format(Me.TextBox1.Value, "0.00")
End If

If TextBox2.Value <> "" Then
B = Format(Me.TextBox2.Value, "0.00")
End If

If TextBox3.Value <> "" Then
C = Format(Me.TextBox1.Value, "0.00")
End If

If TextBox4.Value <> "" Then
D = Format(Me.TextBox1.Value, "0.00")
End If

If TextBox5.Value <> "" Then
E = Format(Me.TextBox1.Value, "0.00")
End If

If A + B + C + D + E <> 100 Then
    MsgBox "Allocation entries must total to 100."
Else
MsgBox "The sum are 100 Now."
    Exit Sub
End If

End Sub

[cschoyer]

Great, thank you!

[cschoyer]

Now, no matter what I enter, I get the msgbox "Allocation entries must equal 100%." - even if they DO equal 100%!!! Please help!

'format percentage boxes
Private Sub Allocation1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Allocation1.Text <> "" Then
        Me.Allocation1.Value = Format(Me.Allocation1 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub
'format percentage boxes
Private Sub Allocation2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Allocation2.Text <> "" Then
        Me.Allocation2.Value = Format(Me.Allocation2 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub
'format percentage boxes
Private Sub Allocation3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Allocation3.Text <> "" Then
        Me.Allocation3.Value = Format(Me.Allocation3 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub
'format percentage boxes
Private Sub Allocation4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Allocation4.Text <> "" Then
        Me.Allocation4.Value = Format(Me.Allocation4 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub
'format percentage boxes
Private Sub Allocation5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Allocation5.Text <> "" Then
        Me.Allocation5.Value = Format(Me.Allocation5 / 100, "0.00%")
    End If
    On Error Resume Next
End Sub

Private Sub EnterButton_Click()
With Worksheets("INVOICES")

    Dim A As Integer
    Dim B As Integer
    Dim C As Integer
    Dim D As Integer
    Dim E As Integer

If Allocation1.Value <> "" Then
A = Format(Me.Allocation1.Value, "0.00")
End If

If Allocation2.Value <> "" Then
B = Format(Me.Allocation2.Value, "0.00")
End If

If Allocation3.Value <> "" Then
C = Format(Me.Allocation3.Value, "0.00")
End If

If Allocation4.Value <> "" Then
D = Format(Me.Allocation4.Value, "0.00")
End If

If Allocation5.Value <> "" Then
E = Format(Me.Allocation5.Value, "0.00")
End If

If A + B + C + D + E <> 100 Then
    MsgBox "Allocation entries must total 100%."
    Exit Sub
End If