Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Multiple textboxes - same code

This is a discussion on Multiple textboxes - same code within the Excel Questions forums, part of the Question Forums category; Hi, I have a number of textboxes, or other boxes, using exactly the same code. In visual basic you can ...

  1. #1
    Board Regular
    Join Date
    Jan 2003
    Posts
    139

    Default Multiple textboxes - same code

    Hi,

    I have a number of textboxes, or other boxes, using exactly the same code. In visual basic you can assign an index to these boxes and create one code where the index number specifies the box you are working with.

    I have tried finding a way to do thing in VBA, but came up against a blank. I realise that this is either not possible or very simple, but right now I am stuck with the 'not possible'. Does anyone know if the 'very simple' is an option. It would greatly decrease the size of my program, make it easier to visualise and not make me change to much each time.
    Of course I refer to subs as much as possible making these routines 3 line routines (sub-call-endsub), but still there are a lot of textbox1_click() routines whereas textbox_click(index) would be nicer.

    Luke

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,589

    Default Re: Multiple textboxes - same code

    VBA doesn't have control arrays like VB. But you can use a Class module. Where are your TextBoxes?

  3. #3
    Board Regular
    Join Date
    Jan 2003
    Posts
    139

    Default Re: Multiple textboxes - same code

    All related textboxes are on the same userform. I may have bundled some of them into frames to be able to differentiate between sets.

    I have not worked with class modules (yet) May open up a brand new door to me. Can you expand??

    say a code like this

    sub datebox1_afterupdate()
    if isdate(datebox1.text) then
    msgbox "correct date"
    else
    msgbox "this is not a date"
    endif
    end sub

    where this goes for say 5 dateboxes
    Luke

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,589

    Default Re: Multiple textboxes - same code

    Here is some sample code:

    Code:
    '   Class module named TBClass
    
    Public WithEvents TBGroup As MSForms.TextBox
    
    Private Sub TBGroup_Change()
        MsgBox TBGroup.Name & " changed"
    End Sub
    
    '   UserForm module
    
    Dim TBs() As New TBClass
    
    Private Sub UserForm_Initialize()
        Dim TBCount As Integer
        Dim Ctrl As Control
        TBCount = 0
        For Each Ctrl In UserForm1.Controls
            If TypeName(Ctrl) = "TextBox" Then
                TBCount = TBCount + 1
                ReDim Preserve TBs(1 To TBCount)
                Set TBs(TBCount).TBGroup = Ctrl
            End If
        Next Ctrl
    End Sub
    That uses the Change event, to which you can add code as required. In the Class module click TBGroup in the left hand dropdown and you will get a list of events in the right hand dropdown. Not all the expected events are available, particularly AfterUpdate, BeforeUpdate, Enter and Exit.

  5. #5
    Board Regular
    Join Date
    Jan 2003
    Posts
    139

    Default Re: Multiple textboxes - same code

    in the meantime started expirimenting with class modules. Works great. Thanks for the tip and for the sample code

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Posts
    184

    Default Re: Multiple textboxes - same code

    Hi Andrew,

    I am geeting Compile error at line ReDim Preserve TBs(1 To

    Thanks.

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,589

    Default Re: Multiple textboxes - same code

    I just tried the code and it worked fine. Make sure you copy all the code into the right modules.

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Posts
    184

    Default Re: Multiple textboxes - same code

    Hi Andrew Poulsom,

    I copied the entire code and pasted in the userform. I do not have any other code on the form.
    I guess I am missing some code. Where do I write code for textbox.

    Please revert.

    Thanks.

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,589

    Default Re: Multiple textboxes - same code

    The first part of the code goes in a Class mdule named TBClass:

    Code:
    '   Class module named TBClass
    
    Public WithEvents TBGroup As MSForms.TextBox
    
    Private Sub TBGroup_Change()
        MsgBox TBGroup.Name & " changed"
    End Sub

  10. #10
    Board Regular
    Join Date
    Nov 2008
    Posts
    184

    Default Re: Multiple textboxes - same code

    Hi Andrew Poulsom,

    I'm sorry, I did not get you.

    I have 4 textboxes - textbox1, textbox2, textbox3, textbox4 and a Command button - commandbutton1.

    The user enters value on textbox1 and textbo3 as 5 and 7 respectively.
    On clicking the Command Button, the output should be produced as 25 and 35 in textbox2 and textbox4 respectively assuming output = input * 5.

    Please let me know the code for above requirement so that I can code accordingly.

    Thanks.

Page 1 of 2 12 LastLast

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