Where should I format my Excel text box controls in my VBA code ?

Hi All,

I've created an Excel 2010 User form and I've been trying to format the VBA text box controls as currency fields :

      Me.Controls("teUnitPrice" & i).Value = Format(Me.Controls("teUnitPrice" & i).Value, "#,##0.00")

But if I enter £300, for example, it doesn't autofill the pence zeroes as I would expect.

Is there a quicker or a better means of applying currency formats to control fields than formatting them individually ? Can they be formatted in Properties ?

Thanks
Toco
TocogroupAsked:
Who is Participating?
 
FaustulusConnect With a Mentor Commented:
Yeah, you got the analysis down correctly. You have to do every item. But once you have it done you tend not to miss the "NumberBox" so much any more, :lol

The concatenation of the two numbers manipulations should be,
Me.Controls("teNet" & i).Value = Format(SalesItemsArray(x, 5), "#,##0.00")
0
 
FaustulusCommented:
Hi Toco,
No, you can't set a number format for a TextBox because, well, it is a Text box. But I am surprised that your code doesn't work. Try this variation:-
Me.Controls("teUnitPrice" & i).Text = Format(Me.Controls("teUnitPrice" & i).Value, "#,##0.00")
0
 
TocogroupAuthor Commented:
Hi Faustalus,

Yes that worked, although it may have been because I only formatted the field in my Initialize procedure, and not in the procedure which populated the control from the array. I was hoping I could format the control just once at startup and it would retain it for other procedures.

I guess there isn't such a thing as a Number box is there ? There should be as it would be useful.

How do I combine these two lines ?

      Me.Controls("teNet" & i) = SalesItemsArray(x, 5)
      Me.Controls("teNet" & i).Value = Format(Me.Controls("teNet" & i).Value, "#,##0.00")

Thanks
Toco
0
 
TocogroupAuthor Commented:
That's great. I can use that structure all the time now. Previously I've always done it separately.

Thanks
Toco
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.