Percentage formats in User Form text boxes

scoha

Active Member
Joined
Jun 15, 2005
Messages
428
I have a user form which includes some text boxes (txtRUL.text for example) where I want to enter a number as a percentage. I divide the number entered by 100 to avoid Excel automatically multiplying by 100 and the result is displayed in the user form as the correct percentage.

Private Sub txtRUL_AfterUpdate()
EnableSave
txtRUL.Value = Format(txtRUL.Value / 100, "0.0%")

(I'm sorry I cant figure out how to format this code as code in this post)

This works well, trouble is when I save and it writes the results to the worksheet, the numbers in the user form revert back to plain unformatted non percentage numbers (ie 5% goes to 0.05)

Any ideas?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi, Scoha,

both Excel97 and Excel2002/XP deliver strings stating the text of txtRUL when I try to rebuild the problem.

Code:
Private Sub cmdtxtRUL2Range_Click()
With Range("A1")
  .Value = CDbl(Left(txtRUL.Text, Len(txtRUL.Text) - 1)) / 100
  .NumberFormat = "0.0%"
End With
End Sub

Private Sub txtRUL_AfterUpdate()
'EnableSave
With txtRUL
  .Value = Format(.Value / 100, "0.0%")
End With
End Sub

Code segments: use [ code ] and [ /code ] without spaces at the beginning and at the end fo the code.

Ciao,
Holger
 
Upvote 0
try
Code:
Private Sub txtRUL_Exit(ByVal Cancell As MSForms.ReturnBoolean)
    Dim temp
    temp = FormatPercent(Val(txtRUL.Value), 2)
    txtRUL.Value = temp
    With Range("a1")
        .Value = temp
        .NumberFormat = "0.00%"
    End With
End Sub
 
Upvote 0
Thanks Jindon - this didnt work and I still get the number displayed in the User Form as a fraction and not a percentage - though it enters as a percentage. In my save routine I have a Put Data routine followd by a Get Data routine which writes to the worksheet and then gets informatrion back from the worksheet so as the User Form always displays whats in the worksheet
 
Upvote 0
1) Can you show me how you actually entring textbox ?
2) Do you use my code with your AfterUpdate code? (your code should be deleted)
 
Upvote 0
Jindon

Here is my "Put" code which writes the contents of the text box to the right spot in my worksheet:

Code:
Private Sub PutData()

Dim r As Long

If IsNumeric(ContractNum.Text) Then
    r = CLng(ContractNum.Text + FirstRow - 1)

Else

    ClearData
    MsgBox "illegal row number"
    Exit Sub

End If

LastRow = FindLastRow

If r > 5 And r <= LastRow Then
    Cells(r, 27) = txtRUL.Text
    Cells(r, 28) = txtRPC.Text
    Cells(r, 32) = txtBld.Text
    Cells(r, 33) = txtFix.Text
    Cells(r, 34) = txtMot.Text
    Cells(r, 35) = txtPla.Text
    Cells(r, 36) = txtTrk.Text
    Cells(r, 37) = txtCom.Text
    Cells(r, 38) = txtOff.Text
    Cells(r, 39) = txtOhd.Text
    Cells(r, 40) = txtTot.Text

    DisableSave
    
Else
       
    MsgBox "Invalid row number"
    
End If

End Sub

and here is my "Get" code which then retrieves the contents of the worksheet (both these are run when I Save the user form:

Code:
Private Sub GetData()

Dim r As Long

FirstRow = 6

If IsNumeric(ContractNum.Text) Then
    r = CLng(ContractNum.Text + FirstRow - 1)
    
Else

    ClearData
    MsgBox "Invalid contract number"
    Exit Sub

End If

LastRow = FindLastRow

If r >= FirstRow And r <= LastRow Then
      
    txtRUL.Text = Cells(r, 27)
    txtRPC.Text = Cells(r, 28)
    txtBld.Text = Cells(r, 32)
    txtFix.Text = Cells(r, 33)
    txtMot.Text = Cells(r, 34)
    txtPla.Text = Cells(r, 35)
    txtTrk.Text = Cells(r, 36)
    txtCom.Text = Cells(r, 37)
    txtOff.Text = Cells(r, 38)
    txtOhd.Text = Cells(r, 39)
    txtTot.Text = Cells(r, 40)
    

DisableSave


    ElseIf r <= 5 Then
    ClearData

Else
ClearData
MsgBox "Invalid row number"

End If
End Sub

Hope this helps you understand my problem
 
Upvote 0
OK
I guess you are talking about PutData...
How are you entering the data in each textbox ?
e.g
12.5% or 0.125 or 12.5....etc
 
Upvote 0
Then simply
Code:
Private Sub txtRUL_Exit(ByVal Cancell As MSForms.ReturnBoolean)
txtRUL.Value = txtRUL.Value & "%"
End Sub
?
 
Upvote 0
Thanks Jindon,

Not quite there yet as it displays correctly in the user form as, say 12.5% but when I save this and write to the worksheet using:

Code:
Cells(r, 27) = txtRUL.Text/[code] 

and then retrieve the data to update the user form using

[code]txtRUL.Text = Cells(r, 27)/[code]

the userform displays this as 0.125.

The above operations are triggered when I run the Save routine.

There is a little problem that if I click back into the userform txt box after I have already entered a number it adds another "%" symbol to the txt box resulting in something like 12.5%%.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top