Multiple textboxes - same code

[lpvdsteen]

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

[Andrew Poulsom]

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

[lpvdsteen]

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

[Andrew Poulsom]

Here is some sample 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.

[lpvdsteen]

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

[hiteshkataria]

Hi Andrew,

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

Thanks.

[Andrew Poulsom]

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

[hiteshkataria]

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.

[Andrew Poulsom]

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

'   Class module named TBClass

Public WithEvents TBGroup As MSForms.TextBox

Private Sub TBGroup_Change()
    MsgBox TBGroup.Name & " changed"
End Sub

[hiteshkataria]

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.