Class module for textbox will not work??

[DRJ]

I was trying to group together several textboxes on a userform to group together their before update events to check for proper values. Anyways I get to the point when I start a class module and put

Public WithEvents TextBoxGroup As TextBox

When I try to compile excel gets an error stating that the object does not source automation events. So can text boxes be grouped like this? I was able to do the same thing with command buttons and toggle buttons, but maybe there is a different way to do text boxes??

 

[Greg Truby]

Jacob,

As long as you have a reference to MSForms library you should be able to do that. I got the following to compile:

Public WithEvents tbxTest As MSForms.TextBox

The problem you'll run into is that in class modules the menu of events is (for some reason I don't know) restricted. Looks like for a textbox control in a class module, the only events you'll be able to trap are:<ul>[*]BeforeDragOver[*]BeforeDropOrPaste[*]Change[*]DblClick[*]DropButtonClick[*]Error[*]KeyDown[*]KeyPress[*]KeyUp[*]MouseDown[*]MouseMove[*]MouseUp[/list]And change fires for every ruddy change, so it's tough to use. I've been working on similar problems. I'd love to be able to figure out a way to trap on Enter/Exit events on multiple textboxes at once, but I haven't figured out a good way (I ended up just using the VBIDE / VBComponent tools to insert a bunch of code into the userform - ugh). I also tried using a generic MSForms.Control object in a class module because that does give you Enter/Exit events. But VBA would not let me assign a textbox to that generic control. :x

If you do figure out a way to pull this off in a class module, do post back, I'd be interested in seeing it. Good luck.

 

[DRJ]

I couldn't see MSForms as a reference in VBE. I have Excel 2002. Is there something that I need to download, or am I just going blind. Also this is for Excel VBA, and not VB right?

I wonder why there is no after update or before update, or enter/exit event. seem to me like that would be more usefull then mouse down.

 

[Jaafar Tribak]

Hi,

The MSForms Library becomes available just by adding a new UserForm to your project.

 

[Greg Truby]

Excel VBA. Mine is listed as "Microsoft Forms 2.0 Object Library" which - on my system - points to C:\WINNT\System32\FM20.DLL

And I could not agree more - I would think Enter and Exit loads more useful than MouseDown!

 

[DRJ]

Hi,

The MSForms Library becomes available just by adding a new UserForm to your project.

ok I see. I was just playing around in a blank wb and there was no userform so nothing was working. I put in a userform and MSForms.Textbox works. Nothing usefull to do with it yet though

 

[DRJ]

Thanks for the help. I was able to get something working. I only want numbers in several textboxes so I use this

In the userform

Option Explicit

Dim TextBoxes(1 To 15)  As New UserFormControlGroupClass

Private Sub UserForm_Initialize()

Dim x               As Integer

    For x = 1 To 15
        Set TextBoxes(x).TextBoxGroup = PayCalculator.Controls("TextBox" & x)
    Next x
    Call FormatUserForm(Me.Caption)

End Sub

Then in the class module

Option Explicit

Public WithEvents TextBoxGroup As MSForms.TextBox

Private Sub TextBoxGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    If KeyAscii < 46 Or KeyAscii > 57 Or KeyAscii = 47 Then KeyAscii = 0

End Sub

This works good to only allow numbers and decimal. Now all I need is a way to format the textboxes as 0.00 after the numbers are updated. I currently just duplicate the code for each individual textbox to do this, but was hoping for a simple way.

 

[Jaafar Tribak]

Hi,

Nice Subject Maybe some Sublcassing is needed to trap the Exit/Enter events of the TextBoxes globally. I haven't tried this yet but I will be interested to know if it can be done.

Regards.