Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting for Numbers & Currency in textbox
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 --- ~~ Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting for Numbers & Currency in textbox
Dugless wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting for Numbers & Currency in textbox
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TextBox on a userform - format to currency & percent | Excel Discussion (Misc queries) | |||
Textbox Formatting | Excel Discussion (Misc queries) | |||
TextBox Formatting | Excel Discussion (Misc queries) | |||
Userform Textbox Currency Format Problems | Excel Programming | |||
Textbox on a userform.. dispaly as currency | Excel Programming |