Formatting Textbox, passing values to worksheet

[Craig]

Hi There, I have a userform that when run picks up values from a worksheet.
I need to be able to enter values into these textbox's which would update
the value on the worksheet. I also want the textbox's to format itself. So
if I enter 150 it would format itself to $150.00 when I press enter... on
enter I need to switch to the next textbox.
My textbox's are numbered textbox1 - textbox50 and the worksheet reference
is sheet1 A100 - A150.
I also need textbox51 to sum these textbox's or worksheet ranges as I change
the values of the textbox's.

I'm using the EnterKeyBehavior=True, I have set each textbox's ControlSource
to the Worksheet Range.
I'm using Textbox?.text=Format(Textbox?.text,"#,####.00")
If I type in 150 and hit enter... it still says 150. If I click on the
textbox with the mouse it formats correctly until I leave the textbox. I've
also placed the format commands into the textbox.Change event.

What would be the best way to handle this situation?

Thanks in Advance!
Craig

 

[Leith Ross]

Hello Craig,

You were pretty close to solving the problem. Just move the Format
statement into the TextBox's After_Update event. Use "Currency" for
money formatting because it is internationally aware.


Code:
--------------------

Private Sub TextBox1_AfterUpdate()

TextBox1.Text = Format(TextBox1.Text, "Currency")

End Sub

 

[Guest]

Hi,

Arrange your Tab order of your form objects.

Right-click on the form then select tab order
Move the textboxes up the ladder
you can only move ten at a time either all at once or one at a time, click
okay then open the tab order box again to do the others, ten at a time. The
tab order Box has to update its references. Doing more than 10 at a time may
corrupt your Tab Order Box and your whole workbook.

The user can then press the tab key to move to the next box.


In userform_initialize sub
textbox1.setfocus


dim varSum
varsum="=sum(A100:A150)"
textbox51.text=varsum

Surely you have a sum formula at A151 then
textbox51.text=range("A151").value



- -Mark