Editing Textbox After Format??

[henryhbb]

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

 

[Doug Glancy]

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]

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

Thanks Anyway

Henry

 

[Tom Ogilvy]

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