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.
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
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 SubThe 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
