Formatting Textbox, passing values to worksheet

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

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.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

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



"Craig" wrote:

> 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
>
>
>