Announcement

Collapse
No announcement yet.

Textbox Class Object

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Textbox Class Object



    Hi All,

    I recently had a request for a better method of controlling multiple textboxes on a userform. "I have lots of textboxes on a userform I want the user to only be able to enter numbers in all of them!"

    The solution: Making use of a class module. The method below will alow you to handle the event for all textboxs at once without having to add events for each individual textbox on the form.

    Add a class module and name it "clsObjHandler"

    Code:
    Option Explicit
    Private WithEvents tbxCustom1 As MSForms.TextBox    'Custom Textbox
    Public Property Set Control(tbxNew As MSForms.TextBox)
        Set tbxCustom1 = tbxNew
    End Property
    Private Sub tbxCustom1_Change()
    'Message Box To Display Which Textbox Was Changed
        MsgBox "You added A Number To: " & tbxCustom1.Name
    'This is just to show you can handle multiple events of the textbox
    End Sub
    Private Sub tbxCustom1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    'Allow only Numbers To be Entered Into Textbox
        Select Case KeyAscii
        Case 46 To 57
        Case Else
            KeyAscii = 0
        End Select
    End Sub
    Private Sub Class_Terminate()
    'Destroy The Class Object And Free Up Memory
        Set tbxCustom1 = Nothing
    End Sub
    Add a userform with as many textboxes as necessary and the following code:

    Code:
    Option Explicit
    Dim colTbxs As Collection  'Collection Of Custom Textboxes
    Private Sub UserForm_Initialize()
        Dim ctlLoop As MSForms.Control
        Dim clsObject As clsObjHandler
    
        'Create New Collection To Store Custom Textboxes
        Set colTbxs = New Collection
        'Loop Through Controls On Userform
        For Each ctlLoop In Me.Controls
            'Check If Control Is A Textbox
            If TypeOf ctlLoop Is MSForms.TextBox Then
                'Create A New Instance Of The Event Handler CLass
                Set clsObject = New clsObjHandler
                'Set The New Instance To Handle The Events Of Our Textbox
                Set clsObject.Control = ctlLoop
                'Add The Event Handler To Our Collection
                colTbxs.Add clsObject
            End If
        Next ctlLoop
    End Sub
    Private Sub UserForm_Terminate()
    'Destroy The Collection To Free Memory
        Set colTbxs = Nothing
    End Sub
    Hope this helps someone out.
    Attached Files
    Last edited by Reafidy; December 11th, 2007, 07:30. Reason: Thanks SHG
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

  • #2
    Re: Textbox Class Object

    Nice job, Reafidy!

    I'm going to have to ponder the code to understand it better, but do have one question: shouldn't
    Code:
        Dim clsObject As New clsObjHandler
    be
    Code:
        Dim clsObject As clsObjHandler
    .. so that you don't create an orphaned instance of the class when clsObject is declared?

    Edit: Well, two questions: Would you explain how this line works:
    Code:
                Set clsObject.Control = ctlLoop
    Entia non sunt multiplicanda sine necessitate.

    Comment


    • #3
      Re: Textbox Class Object

      Ahh silly me - Yes it certainly should be thanks.

      Im not very good at explaining things.
      Code:
      Set clsObject.Control = ctlLoop
      it tells the class to handle events for the textbox.
      An alternative would be:
      Code:
      Set clsObject.tbxCustom1 = ctlLoop
      and changing the withevents to public and removing the property set control:
      Code:
      Public WithEvents tbxCustom1 As MSForms.TextBox   
      'Public Property Set Control(tbxNew As MSForms.TextBox)
      '    Set tbxCustom1 = tbxNew
      'End Property
      Reafidy

      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

      Comment


      • #4
        Re: Textbox Class Object

        I'm going to have to digest that some more.

        Thanks again, Reafidy.
        Entia non sunt multiplicanda sine necessitate.

        Comment


        • #5
          Re: Textbox Class Object

          Nice work, thanks Reafidy!

          Comment


          • #6
            Re: Textbox Class Object

            Hi,
            @Reafidy sorry for digging up the thread, but I've got a question. In your example we've got 4 textboxes. What should I do if I want put value for example
            from TextBox1 to cell A1,
            from TextBox2 to cell A2,
            from TextBox3 to cell A3
            and so on.

            I came up with
            Code:
            row = Right(tbxCustom1.Name, 1)
            and use it later as
            Code:
            cells(row, 1).value = tbxCustom1.value
            but it's very non proffesional way :D

            Comment


            • #7
              Hello Reafidy ...

              Just discovered you very handy TextBox class ...!!!

              Like it very much ...

              Thanks a lot Reafidy
              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

              Comment


              • #8


                Your welcome!
                Reafidy

                Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                Comment

                Working...
                X