Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1757
  • Last Modified:

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
0
Tocogroup
Asked:
Tocogroup
  • 2
  • 2
1 Solution
 
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
 
FaustulusCommented:
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
 
TocogroupAuthor Commented:
That's great. I can use that structure all the time now. Previously I've always done it separately.

Thanks
Toco
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now