Format textbox on exit

[Michael Malinsky]

I have the following:

Private Sub tbAnnualInterest_Exit(ByVal Cancel As
msforms.ReturnBoolean)

tbAnnualInterest = Format(tbAnnualInterest / 100, "0.0000%")

End Sub


which works fine the first time a value (a percentage entered as 4.9
vs. .049) is entered into the textbox. However, if the textbox is
entered and exited without entering a new value, I get a Type mismatch
error. I've tried using various combinations of Val and/or CStr and
anything else I thought might be useful.

Any suggestions, as always, are greatly appreciated.

TIA
Mike

 

[Bob Phillips]

Mike,

Don't do it. Add a button to accept the value and format it

Private Sub cmdAnnualInterest_Click()
tbAnnualInterest.Text = Format(tbAnnualInterest.Text / 100, "0.0000%")
End Sub

and just tab back in if not okay.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Michael Malinsky]

Bob,

I played with it for a while and determined that tacking on the "%"
like I originally did was the problem. So I modified my original code
and added an Enter event. The revised code is as follows:

Private Sub UserForm_Initialize

tbAnnualInterest = Format(0#, "0.0000%")

End Sub
-------
Private Sub tbAnnualInterest_Enter()

tbAnnualInterest = Format(Format(tbAnnualInterest, "0.0000") * 100,
"0.0000")

End Sub
-------
Private Sub tbAnnualInterest_Exit(ByVal Cancel As
msforms.ReturnBoolean)

tbAnnualInterest = Format(tbAnnualInterest, "0.0000") & "%"

End Sub
-------

I have a "nested" format statement in my Enter event, but it seems to
do the trick. I don't forsee any problems doing it this way.

Any thoughts?

Thanks,
Mike

 

[Bob Phillips]

Mike,

I played around with this, and it works well doesn't it? Couldn't see any
real problems. I just didn't like the textbox being loaded with 0.0000, but
that may just be me.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Michael Malinsky]

Bob,

That's the best I came up with at this point. It seems that if I leave
the "%" sign there, then the value automatically gets converted to a
percentage (i.e., 4.9% becomes 0.049). Then when you repeatedly enter
and exit the textbox, the number kept getting divided by 100 (0.049,
0.00049, etc.). The only time the textbox contains 0.0000 is when it
is entered. The rest of the time it has the "%" on the end. I'll just
have to specify on the userform exactly what needs entered.

Thanks.