Editing Textbox After Format??

I have several userforms built with about 60 textboxes. I have the textboxes
bound to cells on a worksheet usung the controlsource property. I have also
created a class module for all the textboxes so that events can occurr inside
of one code for all of them. In the class moduel I have the textboxes
formatted with the Change event with the following code:

Public WithEvents textgroup As MSForms.TextBox

Private Sub textgroup_Change()
textgroup.Value = Format(textgroup.Value, "Standard")
End Sub

This works fine when the form first loads, the values from the linked cells
load into the form and they are formatted properly. However, when the user
goes to change a value it will only let them enter 3 digits. The format seems
to only allow entry of numbers after the decimal point. I've tried using
other events to handle the format such as AfterUpdate and Exit and they
either do not work or they are unavailable to the new class. Is there any
way to make this work? The only other way that I can think of is to write a
separate format code for each textbox, and needless to say that would take a
while and make the code rather sloppy. I would greatly appreciate any help
anyone can offer.

Thanks

Henry

Henry,

I spent some time with this and couldn't find an answer. I then Googled
"exit class event textbox excel" and saw that Tom Ogilvy and others
confirmed that there is no way to trap exit (or those other) events for
textboxes in a class module. I didn't see any solutions from them that
would work for you.

I tried different formats and other events, but couldn't come up with
anything that worked as you want.

Doug

"henryhbb" <henryhbb.1q6lpi_1118022071.8666@excelforum-nospam.com> wrote in
message news:henryhbb.1q6lpi_1118022071.8666@excelforum-nospam.com...
>
> I have several userforms built with about 60 textboxes. I have the
> textboxes
> bound to cells on a worksheet usung the controlsource property. I have
> also
> created a class module for all the textboxes so that events can occurr
> inside
> of one code for all of them. In the class moduel I have the textboxes
>
> formatted with the Change event with the following code:
>
> Public WithEvents textgroup As MSForms.TextBox
>
> Private Sub textgroup_Change()
> textgroup.Value = Format(textgroup.Value, "Standard")
> End Sub
>
> This works fine when the form first loads, the values from the linked
> cells
> load into the form and they are formatted properly. However, when the
> user
> goes to change a value it will only let them enter 3 digits. The format
> seems
> to only allow entry of numbers after the decimal point. I've tried
> using
> other events to handle the format such as AfterUpdate and Exit and they
>
> either do not work or they are unavailable to the new class. Is there
> any
> way to make this work? The only other way that I can think of is to
> write a
> separate format code for each textbox, and needless to say that would
> take a
> while and make the code rather sloppy. I would greatly appreciate any
> help
> anyone can offer.
>
> Thanks
>
> Henry
>
>
> --
> henryhbb
> ------------------------------------------------------------------------
> henryhbb's Profile:
> http://www.excelforum.com/member.php...o&userid=15774
> View this thread: http://www.excelforum.com/showthread...hreadid=376604
>

I appreciate your help on this.....I guess the only way to do this is the long way.

Thanks Anyway

Henry

You migh try something like this in your event:

Private Sub TextBox1_Change()
Dim dblVal As Double
dblVal = CDbl(Replace(textgroup.value, ".", "")) / 100
textgroup.value = Format(dblVal, "Standard")
End Sub

--
Regards,
Tom Ogilvy


"henryhbb" <henryhbb.1q73id_1118045104.7571@excelforum-nospam.com> wrote in
message news:henryhbb.1q73id_1118045104.7571@excelforum-nospam.com...
>
> I appreciate your help on this.....I guess the only way to do this is
> the long way.
>
> Thanks Anyway
>
> Henry
>
>
> --
> henryhbb
> ------------------------------------------------------------------------
> henryhbb's Profile:
http://www.excelforum.com/member.php...o&userid=15774
> View this thread: http://www.excelforum.com/showthread...hreadid=376604
>