VBA Tip: Use a variable as the name of a control

To replace the control name with a variable, use Controls.


For example, to remove the contents of 20 TextBox, you can write:

Private Sub CommandButton_vider_Click()
   
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox12.Value = ""
    TextBox13.Value = ""
    TextBox14.Value = ""
    TextBox15.Value = ""
    TextBox16.Value = ""
    TextBox17.Value = ""
    TextBox18.Value = ""
    TextBox19.Value = ""
    TextBox20.Value = ""
   
End Sub

But thanks to Controls, we can reduce the code to 3 lines:

Private Sub CommandButton_vider_Click()
   
    For i = 1 To 20
        Controls("TextBox" & i).Value = ""
    Next
   
End Sub