Userform Formatting

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

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.
 
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
 
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)
 
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
 
hey I could use a loop to get around this. How would I
set it up to say If error go to next?
 
Todd Huttenstine said:
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?
Click to expand...

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
 
Thank you. That worked beautiful!

-----Original Message-----


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




.
Click to expand...
 
Back
Top