Textbox1.Value Question

[PaulW]

Hi there,

I am working on a UserForm with three Textboxes (Texbox1, Textbox2 & Textbox3)
Each of the Textboxes are to store a number and display as a %.

What I am struggling to complete is for each of the three boxes when
combined do not total over 100% or over 100% invidiually.

Please see my code below so far:

Private Sub TextBox1_AfterUpdate()

a = TextBox1.Value
If Right(a, 1) = "%" Then a = Left(a, 2)
b = TextBox2.Value
If Right(b, 1) = "%" Then b = Left(b, 2)
c = TextBox3.Value
If Right(c, 1) = "%" Then c = Left(c, 2)

d = a + b + c
MsgBox (d)

If TextBox1.Value = "" Then Exit Sub
If Right(TextBox1.Value, 1) <> "%" Then
If TextBox1.Value > 100 Then
MsgBox ("Error")
TextBox1.Value = ""
Else: TextBox1.Value = TextBox1.Value & "%"
End If
End If
End Sub
Private Sub TextBox2_AfterUpdate()
a = TextBox1.Value
If Right(a, 1) = "%" Then a = Left(a, 2)
b = TextBox2.Value
If Right(b, 1) = "%" Then b = Left(b, 2)
c = TextBox3.Value
If Right(c, 1) = "%" Then c = Left(c, 2)

d = a + b + c
MsgBox (d)

If TextBox2.Value = "" Then Exit Sub
If Right(TextBox2.Value, 1) <> "%" Then
If TextBox2.Value > 100 Then
MsgBox ("Error")
TextBox2.Value = ""
Else: TextBox2.Value = TextBox2.Value & "%"
End If
End If
End Sub
Private Sub TextBox3_AfterUpdate()
a = TextBox1.Value
If Right(a, 1) = "%" Then a = Left(a, 2)
b = TextBox2.Value
If Right(b, 1) = "%" Then b = Left(b, 2)
c = TextBox3.Value
If Right(c, 1) = "%" Then c = Left(c, 2)

d = a + b + c
MsgBox (d)

If TextBox3.Value = "" Then Exit Sub
If Right(TextBox3.Value, 1) <> "%" Then
If TextBox3.Value > 100 Then
MsgBox ("Error")
TextBox3.Value = ""
Else: TextBox3.Value = TextBox3.Value & "%"
End If
End If
End Sub


Many thanks
Dan

 

[Nigel]

Try taking the Val(TextBox1.Value) this removes any trailing text, so
whether you have 100% or 100 the value returned is 100.

Check each textbox value so that they individually do not exceed 100 and
collectively sum to <=100

Following code may help (untested)

d = 0
For x = 1 to 3
With Controls("TextBox_" & x)
if Val(.Value) > 100 then
MsgBox "Error in TextBox_" & x
Else
d = d + Val(.value)
.value = Val(.Value) & "%"
End If
Next
If d <= 100 then
MsgBox d
Else
MsgBox "Error value sums to > 100"
End if

 

[PaulW]

Thanks Nigel,

I couldn't get your code to work (Not used For and Next before) but the
Val(textbox.value) works wonderfully with a slight rejig of the code.

Thanks again
Dan