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.

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.

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.

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.



.

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.



.

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.



.

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

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




.