Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Textbox1.Value Question
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 130
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 735
Default Textbox1.Value Question
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 130
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
TextBox1.SetFocus not responding excelnut1954 Excel Programming 2 May 30th 06 10:13 PM
TextBox1 to Label4 damorrison Excel Discussion (Misc queries) 5 March 12th 06 04:05 PM
alternative to TextBox1.Activate jose luis Excel Programming 1 June 4th 05 02:48 AM
x = textbox1.value (somethings wrong) CAA[_18_] Excel Programming 6 March 5th 04 03:24 PM
UserForm1.Textbox1.SetFocus Question Zane Greer Excel Programming 1 September 14th 03 11:59 AM


All times are GMT +1. The time now is 09:34 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement