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
              To say "Thank You" for the help received ...Just click on the "Like" icon ...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