Trouble formatting for percentage in Userform

Peter Rattigan

Board Regular
Joined
Oct 18, 2004
Messages
77
I am trying to build an input form for a Worksheet. I have a column of figures that are formatted as percentage on the sheet. I keep running into trouble on the input form in trying to make the input work without type mismatch errors. I have tried many variations and my code has evolved to that shown below.
1. I want to be able to input a whole number to make it come out as a % ie input 5 to get 5%.

2. I also need to be able to delete a value or at least make it zero without generating an error.

3. I need the total at the bottom of the column on the form to reflect the total on the worksheet as each figure in the column is entered.

Code:
Private Sub InputField_AfterUpdate()
If Me.inputfield > 0 Then Me.inputfield = FormatPercent((Me.inputfield / 100), 1) Else Me.inputfield = 0
Me.TotalsField = FormatPercent(Sheets("sheet1").Range("o15").Value, 1)
End Sub

The above code generally does all of these things but will allow the input once and then starts throwing up type mismatch errors on the second time round.

I also have have the formatting code attached to the form activate event and the TextBox Change events to cover each incident.

I am afraid I am more used to Access Forms which allow each field to be formatted in its own properties without all this extra coding. Have I missed something as I have found no other way to format for currency and percentage.

Hope someone can help.
Thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,136,370
Messages
5,675,365
Members
419,565
Latest member
Phil57

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
Top