Results 1 to 8 of 8

Class module for textbox will not work??

This is a discussion on Class module for textbox will not work?? within the Excel Questions forums, part of the Question Forums category; I was trying to group together several textboxes on a userform to group together their before update events to check ...

  1. #1
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default Class module for textbox will not work??

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

  2. #2
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default Re: Class module for textbox will not work??

    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:
      [*]BeforeDragOver[*]BeforeDropOrPaste[*]Change[*]DblClick[*]DropButt*******[*]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.
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  3. #3
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    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.

  4. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default Re: Class module for textbox will not work??

    Hi,

    The MSForms Library becomes available just by adding a new UserForm to your project.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #5
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default Re: Class module for textbox will not work??

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

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  6. #6
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default Re: Class module for textbox will not work??

    Quote Originally Posted by rafaaj2000
    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

  7. #7
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    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

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

    Code:
    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.

  8. #8
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default Re: Class module for textbox will not work??

    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.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com