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]

Try:

'   Class module named TBClass

Public WithEvents TBGroup As MSForms.TextBox
 
Private Sub TBGroup_Change()
    With TBGroup
        UserForm1.Controls("TextBox" & Val(Right(.Name, Len(.Name) - Len("TextBox"))) + 1).Text = Val(.Text) * 5
    End With
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
            If Val(Right(Ctrl.Name, Len(Ctrl.Name) - Len("TextBox"))) Mod 2 = 1 Then
                TBCount = TBCount + 1
                ReDim Preserve TBs(1 To TBCount)
                Set TBs(TBCount).TBGroup = Ctrl
            End If
        End If
    Next Ctrl
End Sub

 

[hiteshkataria]

Hi Andrew Poulsom,

Thanks for the code.

I exactly copied the code and pasted. Howvere on pressing F5, it gives an Compile error : Variable not defined. And follwing code is selected "ReDim Preserve TBs(1 To" with yellow shade on Private Sub UserForm_Initialize().

Could you please let me know, is there any changes in settings is required or so.

Thanks.

 

[Andrew Poulsom]

Insert a class module and change its name to TBClass. Paste this code into the module:

Public WithEvents TBGroup As MSForms.TextBox
 
Private Sub TBGroup_Change()
    With TBGroup
        UserForm1.Controls("TextBox" & Val(Right(.Name, Len(.Name) - Len("TextBox"))) + 1).Text = Val(.Text) * 5
    End With
End Sub

Paste this code into your 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
            If Val(Right(Ctrl.Name, Len(Ctrl.Name) - Len("TextBox"))) Mod 2 = 1 Then
                TBCount = TBCount + 1
                ReDim Preserve TBs(1 To TBCount)
                Set TBs(TBCount).TBGroup = Ctrl
            End If
        End If
    Next Ctrl
End Sub

 

[hiteshkataria]

Hi Andrew Poulsom,

Thanks for the steps.
Its working, however not as I thought.
The value in textbox2 gets changed on typing values in textbox1. The value in textbox2 is not generated on click of a button. Same case for other textbox.

Thanks.

 

[Andrew Poulsom]

If you want to update your TextBoxes when you click a CommandButton, just put some code in the CommandButton's Click event procedure. No need for a Class module there.

 

[hiteshkataria]

Hi Andrew Poulsom,

Could you please help me to code for the following:
2 Input textboxes - txtCost1, txtRate1
1 Output textbox - txtSale1 (=txtCost1 * txtRate1)

And in the same way I need to have txtCost2, txtRate2 and txtSale2 and so on for which the Sale is equal to Cost * Rate.

Thanks.

 

[Andrew Poulsom]

Like this?

Private Sub CommandButton1_Click()
    Dim i As Integer
    With Me
        For i = 1 To 2
            .Controls("TxtSale" & i).Text = Val(.Controls("TxtCost" & i).Text) * Val(.Controls("TxtRate" & i).Text)
        Next i
    End With
End Sub

 

[hiteshkataria]

Excellent!

Thanks a lot.