Format text in Textbox Control in Excel

[mpm32]

I'm using Excel to build a process flow. I've created process boxes where I have grouped the process box along with several text boxes that pull data(linked cells) from another sheet. This way, the data moves with the box when/if I need to move it.

I would like to format the data in the boxes; one has transactions where I would like to format like this 12,000. One has costs where I would like to format like this $3.70.

I am not sure how to go about this.

I'm pretty sure I have to write something in VB but I'm not sure how or where.

Any help would be appreciated.

 

[SkipVought]

Hi,

Check out the Format function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

Ok, I did but I'm don't think what I tried is working.

Private Sub TotalTandTrans_Change()
TotalTandTrans.Text = Format(TotalTandTrans.Text, standard)
End Sub

I think I'm missing something.

 

[PHV]

Tip: use the Option Explicit instruction.
Have you tried this instead ?
TotalTandTrans.Text = Format(TotalTandTrans.Text, "standard")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

for instance...

Private Sub TotalTandTrans_Change()
TotalTandTrans.Text = Format(TotalTandTrans.Text, "#,###")
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Generally, I do not like to work using linked cells. VBA has its own internal formats that are a source of potential mess and are out of control for number/data <=> text conversions.
The more secure is the 'Change', 'LostFocus' or 'KeyDown' events for the control, you can validate what is passed to the cell.
In case of transfer data to/from worksheets, it's also a question of the application design. As a rule there is an input worksheet (cells), calculation sheets (can be hidden) and the output (summary) sheet. Sometimes input and output are together. And it's easy to duplicate and format worksheet data with references.


combo

 

[mpm32]

I got this to work;

Private Sub TextBox36_Change()
TextBox36.Text = Format(TextBox36.Text, "#,0.")
End Sub

But it only works when I make a change in the linked cell. What event should I use if I want the text box to display the format all the time?

I tried MouseMove, Open, KeyPress, Enter, Current.

The only one that works is Change but I don't want to have to make a change in each cell just to have the text box display the format correctly.

Any thoughts?