Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Textbox Class Object

  1. #1
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,284

    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"

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

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Reafidy; December 11th, 2007 at 07:30. Reason: Thanks SHG

  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    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
    VB:
    Dim clsObject As New clsObjHandler 
    
    
    be
    VB:
    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:
    VB:
    Set clsObject.Control = ctlLoop 
    
    
    Entia non sunt multiplicanda sine necessitate.

  3. #3
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,284

    Re: Textbox Class Object

    Ahh silly me - Yes it certainly should be thanks.

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

  4. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,329

    Re: Textbox Class Object

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

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

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,797

  6. #6
    Join Date
    5th July 2017
    Posts
    1

    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
    VB:
    row = Right(tbxCustom1.Name, 1) 
    
    
    and use it later as
    VB:
    cells(row, 1).value = tbxCustom1.value 
    
    
    but it's very non proffesional way :D

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Class Object With Properties & Methods
    By Fruffbear in forum Excel General
    Replies: 1
    Last Post: June 20th, 2008, 20:37
  2. Creating a class ( object )
    By iwrk4dedpr in forum Excel General
    Replies: 5
    Last Post: January 20th, 2006, 06:16
  3. array property of a class object
    By svuille in forum Excel General
    Replies: 6
    Last Post: June 8th, 2005, 16:41

Bookmarks

Posting Permissions

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