Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Formatting
Below is code that inserts the value of the specified
textbox into the specified cell. Private Sub CommandButton2_Click() Worksheets("Pay Calculator").Range("AA1").Value = TextBox1.Value Worksheets("Pay Calculator").Range("AA2").Value = TextBox2.Value Worksheets("Pay Calculator").Range("AA3").Value = TextBox3.Value Worksheets("Pay Calculator").Range("AA4").Value = TextBox4.Value UserForm1.Hide End Sub I have formatted the cells in percentages rounded off to 2 decimal places. For instance if I were to enter 10 directly in the cell, the cell would show 10%. So now if I were to enter 10 in TextBox1, I want it to put 10% in cell AA1, instead it puts 1000% in the cell, however if I were to enter 10% in TextBox1, it then puts 10% in cell AA1 which is what I want, but I dont want to have to type the % in. Is there a way I can make it to where no matter what is typed in the text box it will automatically have the % on the end of the number in the textbox. Or is it possible to when I type in a number in the textbox without a % on the end, it will automatically put 10% in the cell? What is the code for these 2 methods? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Formatting
Private Sub CommandButton2_Click()
Dim i as Long, sStr as String With Worksheets("Pay Calculator").Range("AA1") for i = 1 to 4 sStr = Me.Controls("TextBox" & i).Value if instr(sStr,"%") then .offset(i-0,0).Value = sStr Else .offset(i-0,0).Value = cdbl(sStr)/100 End if Next End With Userform1.Hide End Sub -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... Below is code that inserts the value of the specified textbox into the specified cell. Private Sub CommandButton2_Click() Worksheets("Pay Calculator").Range("AA1").Value = TextBox1.Value Worksheets("Pay Calculator").Range("AA2").Value = TextBox2.Value Worksheets("Pay Calculator").Range("AA3").Value = TextBox3.Value Worksheets("Pay Calculator").Range("AA4").Value = TextBox4.Value UserForm1.Hide End Sub I have formatted the cells in percentages rounded off to 2 decimal places. For instance if I were to enter 10 directly in the cell, the cell would show 10%. So now if I were to enter 10 in TextBox1, I want it to put 10% in cell AA1, instead it puts 1000% in the cell, however if I were to enter 10% in TextBox1, it then puts 10% in cell AA1 which is what I want, but I dont want to have to type the % in. Is there a way I can make it to where no matter what is typed in the text box it will automatically have the % on the end of the number in the textbox. Or is it possible to when I type in a number in the textbox without a % on the end, it will automatically put 10% in the cell? What is the code for these 2 methods? Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Formatting
Todd,
Percentage is a percentage of 1, so 10 is seen as 1000%. The easiest way to achieve what you want is like so Private Sub CommandButton1_Click() With Worksheets("Pay Calculator") .Range("AA1").Value = TextBox1.Value / 100 .Range("AA2").Value = TextBox2.Value / 100 .Range("AA3").Value = TextBox3.Value / 100 .Range("AA4").Value = TextBox4.Value / 100 UserForm1.Hide End With End Sub You could trap the input, but it is more complex. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is code that inserts the value of the specified textbox into the specified cell. Private Sub CommandButton2_Click() Worksheets("Pay Calculator").Range("AA1").Value = TextBox1.Value Worksheets("Pay Calculator").Range("AA2").Value = TextBox2.Value Worksheets("Pay Calculator").Range("AA3").Value = TextBox3.Value Worksheets("Pay Calculator").Range("AA4").Value = TextBox4.Value UserForm1.Hide End Sub I have formatted the cells in percentages rounded off to 2 decimal places. For instance if I were to enter 10 directly in the cell, the cell would show 10%. So now if I were to enter 10 in TextBox1, I want it to put 10% in cell AA1, instead it puts 1000% in the cell, however if I were to enter 10% in TextBox1, it then puts 10% in cell AA1 which is what I want, but I dont want to have to type the % in. Is there a way I can make it to where no matter what is typed in the text box it will automatically have the % on the end of the number in the textbox. Or is it possible to when I type in a number in the textbox without a % on the end, it will automatically put 10% in the cell? What is the code for these 2 methods? Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Formatting
That works but if a value in one of textboxes shows 10%
for instance, I get a debug error. All the values in the textboxes must be raw numbers. How do I get around this? Also what if I wanted to just type a number in the textboxes and have the % automatically appear on the end? Thanx -----Original Message----- Todd, Percentage is a percentage of 1, so 10 is seen as 1000%. The easiest way to achieve what you want is like so Private Sub CommandButton1_Click() With Worksheets("Pay Calculator") .Range("AA1").Value = TextBox1.Value / 100 .Range("AA2").Value = TextBox2.Value / 100 .Range("AA3").Value = TextBox3.Value / 100 .Range("AA4").Value = TextBox4.Value / 100 UserForm1.Hide End With End Sub You could trap the input, but it is more complex. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is code that inserts the value of the specified textbox into the specified cell. Private Sub CommandButton2_Click() Worksheets("Pay Calculator").Range("AA1").Value = TextBox1.Value Worksheets("Pay Calculator").Range("AA2").Value = TextBox2.Value Worksheets("Pay Calculator").Range("AA3").Value = TextBox3.Value Worksheets("Pay Calculator").Range("AA4").Value = TextBox4.Value UserForm1.Hide End Sub I have formatted the cells in percentages rounded off to 2 decimal places. For instance if I were to enter 10 directly in the cell, the cell would show 10%. So now if I were to enter 10 in TextBox1, I want it to put 10% in cell AA1, instead it puts 1000% in the cell, however if I were to enter 10% in TextBox1, it then puts 10% in cell AA1 which is what I want, but I dont want to have to type the % in. Is there a way I can make it to where no matter what is typed in the text box it will automatically have the % on the end of the number in the textbox. Or is it possible to when I type in a number in the textbox without a % on the end, it will automatically put 10% in the cell? What is the code for these 2 methods? Thank you. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Formatting
Sorry Tom, I posted a reply to your message in a new
thread. Didnt mean to. -----Original Message----- Private Sub CommandButton2_Click() Dim i as Long, sStr as String With Worksheets("Pay Calculator").Range("AA1") for i = 1 to 4 sStr = Me.Controls("TextBox" & i).Value if instr(sStr,"%") then .offset(i-0,0).Value = sStr Else .offset(i-0,0).Value = cdbl(sStr)/100 End if Next End With Userform1.Hide End Sub -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... Below is code that inserts the value of the specified textbox into the specified cell. Private Sub CommandButton2_Click() Worksheets("Pay Calculator").Range("AA1").Value = TextBox1.Value Worksheets("Pay Calculator").Range("AA2").Value = TextBox2.Value Worksheets("Pay Calculator").Range("AA3").Value = TextBox3.Value Worksheets("Pay Calculator").Range("AA4").Value = TextBox4.Value UserForm1.Hide End Sub I have formatted the cells in percentages rounded off to 2 decimal places. For instance if I were to enter 10 directly in the cell, the cell would show 10%. So now if I were to enter 10 in TextBox1, I want it to put 10% in cell AA1, instead it puts 1000% in the cell, however if I were to enter 10% in TextBox1, it then puts 10% in cell AA1 which is what I want, but I dont want to have to type the % in. Is there a way I can make it to where no matter what is typed in the text box it will automatically have the % on the end of the number in the textbox. Or is it possible to when I type in a number in the textbox without a % on the end, it will automatically put 10% in the cell? What is the code for these 2 methods? Thank you. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Formatting
hey I could use a loop to get around this. How would I
set it up to say If error go to next? -----Original Message----- Todd, Percentage is a percentage of 1, so 10 is seen as 1000%. The easiest way to achieve what you want is like so Private Sub CommandButton1_Click() With Worksheets("Pay Calculator") .Range("AA1").Value = TextBox1.Value / 100 .Range("AA2").Value = TextBox2.Value / 100 .Range("AA3").Value = TextBox3.Value / 100 .Range("AA4").Value = TextBox4.Value / 100 UserForm1.Hide End With End Sub You could trap the input, but it is more complex. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is code that inserts the value of the specified textbox into the specified cell. Private Sub CommandButton2_Click() Worksheets("Pay Calculator").Range("AA1").Value = TextBox1.Value Worksheets("Pay Calculator").Range("AA2").Value = TextBox2.Value Worksheets("Pay Calculator").Range("AA3").Value = TextBox3.Value Worksheets("Pay Calculator").Range("AA4").Value = TextBox4.Value UserForm1.Hide End Sub I have formatted the cells in percentages rounded off to 2 decimal places. For instance if I were to enter 10 directly in the cell, the cell would show 10%. So now if I were to enter 10 in TextBox1, I want it to put 10% in cell AA1, instead it puts 1000% in the cell, however if I were to enter 10% in TextBox1, it then puts 10% in cell AA1 which is what I want, but I dont want to have to type the % in. Is there a way I can make it to where no matter what is typed in the text box it will automatically have the % on the end of the number in the textbox. Or is it possible to when I type in a number in the textbox without a % on the end, it will automatically put 10% in the cell? What is the code for these 2 methods? Thank you. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Formatting
"Todd Huttenstine" wrote in message
... That works but if a value in one of textboxes shows 10% for instance, I get a debug error. All the values in the textboxes must be raw numbers. How do I get around this? Also what if I wanted to just type a number in the textboxes and have the % automatically appear on the end? For that I suggest that you use a textbox event code Private Sub CommandButton1_Click() With Worksheets("Pay Calculator") .Range("AA1").Value = TextBox1.Value .Range("AA2").Value = TextBox2.Value .Range("AA3").Value = TextBox3.Value .Range("AA4").Value = TextBox4.Value Unload UserForm1 End With End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox1 If Right(.Text, 1) = "%" Then .Text = Left(.Text, Len(.Text) - 1) End If .Text = Format(.Text / 100, "0%") End With End Sub Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox2 If Right(.Text, 1) = "%" Then .Text = Left(.Text, Len(.Text) - 1) End If .Text = Format(.Text / 100, "0%") End With End Sub Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox3 If Right(.Text, 1) = "%" Then .Text = Left(.Text, Len(.Text) - 1) End If .Text = Format(.Text / 100, "0%") End With End Sub Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox4 If Right(.Text, 1) = "%" Then .Text = Left(.Text, Len(.Text) - 1) End If .Text = Format(.Text / 100, "0%") End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Formatting
Thank you. That worked beautiful!
-----Original Message----- "Todd Huttenstine" wrote in message ... That works but if a value in one of textboxes shows 10% for instance, I get a debug error. All the values in the textboxes must be raw numbers. How do I get around this? Also what if I wanted to just type a number in the textboxes and have the % automatically appear on the end? For that I suggest that you use a textbox event code Private Sub CommandButton1_Click() With Worksheets("Pay Calculator") .Range("AA1").Value = TextBox1.Value .Range("AA2").Value = TextBox2.Value .Range("AA3").Value = TextBox3.Value .Range("AA4").Value = TextBox4.Value Unload UserForm1 End With End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox1 If Right(.Text, 1) = "%" Then .Text = Left(.Text, Len(.Text) - 1) End If .Text = Format(.Text / 100, "0%") End With End Sub Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox2 If Right(.Text, 1) = "%" Then .Text = Left(.Text, Len(.Text) - 1) End If .Text = Format(.Text / 100, "0%") End With End Sub Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox3 If Right(.Text, 1) = "%" Then .Text = Left(.Text, Len(.Text) - 1) End If .Text = Format(.Text / 100, "0%") End With End Sub Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox4 If Right(.Text, 1) = "%" Then .Text = Left(.Text, Len(.Text) - 1) End If .Text = Format(.Text / 100, "0%") End With End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting and userform | Excel Discussion (Misc queries) | |||
Userform Add | Excel Discussion (Misc queries) | |||
UserForm | Excel Discussion (Misc queries) | |||
UserForm | Excel Discussion (Misc queries) | |||
userform | Excel Programming |