|
Textbox1.Value Question
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 |
Textbox1.Value Question
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 "Nigel" wrote: 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 -- Regards, Nigel "PaulW" wrote in message ... 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 |
| All times are GMT +1. The time now is 06:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com