Formatting for Numbers & Currency in textbox

[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

 

[Bill Manville]

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

 

[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