Editing percentage value in Userform TextBox

Question

I am attempting to create a textbox which allows users to input a percentage between 1-5% in my Excel-VBA Userform. I have created the textbox and added code to restrict the formatting to always show as a percentage. My problem is that when I attempt to edit the default percentage in the textbox by pressing backspace the decimal moves two spaces to the right instead of deleting any present values. Below is a copy of my code. Please note that the Textbox is not locked.

Private Sub PV_change()

PV.Text = Format(PV.Value, "Percent")

End Sub

Why is it necessary to have the textbox *display* the %? It would seem preferable to simply handle the user input in the dependent procedures/functions, and do a conversion there e.g., `CDbl(PV.Value)/100)` — David Zemens, Nov 08 '16 at 21:11
@DavidZemens I suppose it is not necessary but it makes the userform more intuitive to the user if they are clearly aware the value is a %. — Greg, Nov 08 '16 at 21:15
It will make it harder for the user to enter though, as they will have to enter `.05` in order to get `5%` displayed. — YowE3K, Nov 08 '16 at 21:16
It won't work as posted. Try typing 0.05 - I get `0.00%.05` It's because after the first key stroke the text box contains `0.00%` then after the second key it's `0.00%.` which is no longer interperatable as a number and `Format` fails and returns the original string. Better to put a string next to the text box to indicate %, and handle the conversion elsewhere — chris neilsen, Nov 08 '16 at 21:23
@chrisneilsen I wish I could up-vote your comment by about +100 — YowE3K, Nov 08 '16 at 21:25
Just put a *label* next to the textbox, and have the label's caption as "%", otherwise, you're going to have a helluva time trying to intercept keystrokes (backspaces, etc.) and interpret what the user is actually doing. There's a reason most web forms normalize data like SSN, Phone Numbers, etc., and don't let you input the hyphen/dash, and most survey forms ask you to fill in a percent as an integer value, etc. — David Zemens, Nov 08 '16 at 21:37

score 0 · Answer 1 · answered Nov 08 '16 at 21:42

0

You could do your code in the PV_Exit procedure rather than the _Change procedure, but I still think you're asking for trouble this way :)

Private Sub PV_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    PV.Value = Format(PV.Value, "Percent")
End Sub

answered Nov 08 '16 at 21:42

David Zemens

53,033
11
81
130

score 0 · Answer 2 · edited May 23 '17 at 11:53

I agree with the objections made previously, but to show a simple solution without error trapping try this:

Private Sub tbPV_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim dblPV As Double
' don't allow backspace
  If KeyCode = Asc(vbBack) Then Exit Sub
' Change string to Double value (without percent sign)
  dblPV = CDbl(Replace(Me.tbPV.Text, "%", ""))
' Show the formatted result
  Me.tbPV.Text = Format(dblPV, "0.00") & "%"

End Sub

Further Remark Naming a text box 'PV' only doesn' t seem to be state of the art. Adding a prefix - e.g. 'tb' or 'txt'- to all your text boxes gives a hint to to control's original type and could give your code a comprehensible structure.

There are number of naming conventions and divergent opinions, but just to give you an impression have a look at Hungarian in VBA okay? or search within the Stack Overflow's "naming-conventions" tag.

Best Regards

Editing percentage value in Userform TextBox

2 Answers2