CODEWITHSUNDEEP
vbaexcel
neeenway
neeenway

Reputation: 5

Making one set of code (with slight differences) apply to specific textboxes in userform

I have a UserForm where I have multiple rows of textboxes for data entry. Upon finishing entry for a specific textbox in each row, it triggers a macro for another textbox within the same row to be calculated.

The code is:

Private Sub TextBox46_Change()

    If DataInput.TextBox46.Value > 0 And DataInput.TextBox46.Value < 1000 Then
       DataInput.TextBox54.Value = 100 * ((DataInput.TextBox46.Value - DataInput.TextBox14.Value) / (DataInput.TextBox30.Value - DataInput.TextBox14.Value))
       DataInput.TextBox62.Value = 100 - DataInput.TextBox54.Value
    Else: MsgBox ("Revise Inputs")
    End If
    End Sub

Now I need to apply this for 8 other textboxes, except for example if it was TextBox47_Change then all the other textbox numbers in the code must shift up by 1. I have searched online and people have done it but the code did not change. Here,my code has slight differences for each textbox.

Is there a way to repeat this code without just copy and pasting it to each TextBox_Change sub and then changing the numbers.

For further clarification, in the image I have attached, everytime the Dish + Residue Mass column textbox changes then the TSR,VS,FS is calculated for the corresponding row.

Upvotes: 0

Views: 38

Answers (1)

user6432984
user6432984

Reputation:

Create a function that will return a reference to the textboxes based on a number.

Private Sub TextBox46_Change()
    TextBox_ChangeEvent 0
End Sub

Private Sub TextBox47_Change()
    TextBox_ChangeEvent 1
End Sub

Private Sub TextBox48_Change()
    TextBox_ChangeEvent 2
End Sub

Private Sub TextBox49_Change()
    TextBox_ChangeEvent 3
End Sub

Private Sub TextBox50_Change()
    TextBox_ChangeEvent 4
End Sub

Private Sub TextBox51_Change()
    TextBox_ChangeEvent 5
End Sub

Private Sub TextBox52_Change()
    TextBox_ChangeEvent 6
End Sub

Private Sub TextBox53_Change()
    TextBox_ChangeEvent 7
End Sub

Private Sub TextBox_ChangeEvent(Index As Integer)
    If getTxtBox(46 + Index).Value > 0 And getTxtBox(46 + Index).Value < 1000 Then
        getTxtBox(54 + Index).Value = 100 * ((getTxtBox(46 + Index).Value - getTxtBox(14 + Index).Value) / (getTxtBox(30 + Index).Value - getTxtBox(14 + Index).Value))
        getTxtBox(62 + Index).Value = 100 - getTxtBox(54 + Index).Value
    Else: MsgBox ("Revise Inputs")
    End If
End Sub

Function getTxtBox(Index As Integer) As MSForms.TextBox
    Set getTxtBox = DataInput.Controls("TextBox" & Index)
End Function

Upvotes: 0

Related Questions