Formatting for Numbers & Currency in textbox

D

dugless

Hi all,

I was trying to make it easy for users too enter numbers and currency
on a userform by formatting as they entered. The effect is to insert
commas at the thousand marks (e.g: $ 1,000,000). Otherwise it is
difficult to read the numbers.

In a textbox alled t_salary we had the following code:

Private Sub t_salary_Change()

t_salary.Value = Format(t_salary.Value, "$#,##0")

End Sub

This appeared to work however when a certain amount of memory was used
(or when the PC felt like it) this created an infinite loop. When you
look at it that makes sense that it loops (the change in format sets
off the change event). Interestly when you step through it does not
loop.

You can format on the exit (or some other event) but the idea was to
assist with ENTRY of the large numbers.

Any ideas??

Cheers
Dug
 
B

Bill Manville

Dugless said:
Private Sub t_salary_Change()

t_salary.Value = Format(t_salary.Value, "$#,##0")

End Sub

This appeared to work however when a certain amount of memory was used
(or when the PC felt like it) this created an infinite loop

Try this

Dim ItsMe As Boolean ' at top of module

Private Sub t_salary_Change()
If ItsMe Then Exit Sub
ItsMe = True
t_salary.Value = Format(t_salary.Value, "$#,##0")
ItsMe = False
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
J

Jan Karel Pieterse

Hi Dugless,

This appeared to work however when a certain amount of memory was used
(or when the PC felt like it) this created an infinite loop. When you
look at it that makes sense that it loops (the change in format sets
off the change event). Interestly when you step through it does not
loop.

You can format on the exit (or some other event) but the idea was to
assist with ENTRY of the large numbers.

Any ideas??

Declare a variable at the top of the form's module:

Dim bDisableEvents as Boolean

Now in your change event:

Private Sub t_salary_Change()
If bDisableEvents Then Exit Sub
bDisableEvents=True
t_salary.Value = Format(t_salary.Value, "$#,##0")
bDisableEvents=False
End Sub


Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top