Control Arrays in VBA?
I understand it should be possible to create a control array in VB6 to make changes to multiple controls on a single from by looping through the index. However, I'm using Excel VBA, and I have simple changes to a form based upon choices in a combo box, and I'm not able to find a way to loop through multiple text boxes or labels to make changes without writing code over and over again (ugh!) This should be simple, maybe I just need some syntax help. . .here's what I have:
Ex:
Private Sub CmbBox1_Change()
Select Case CmbBox1.Value
Case "Spread"
TxtBox6.Enabled = True
TxtBox6.BackColor = QBColor(15)
TxtBox7.Enabled = True
TxtBox7.BackColor = QBColor(15)
TxtBox8.Enabled = True
TxtBox8.BackColor = QBColor(15)
TxtBox9.Enabled = True
TxtBox9.BackColor = QBColor(15)
TxtBox10.Enabled = True
TxtBox10.BackColor = QBColor(15)
End Select
End Sub
As you can see, the text box are named sequentially. Shouldn't it be fairly easy to loop through the text box names by incrementing a variable and adding it to the name? i.e.
Dim inti As Integer
For i 6 To 10
TxtBox[i].Enabled = True
TxtBox[i].BackColor = QBColor(15)
Next i
This would make sense to me, but each time I try this method I continue to get "Invalid Qualifier". Any suggestions? As an added item, I have not been able to implement any control arrays, and have no experience doing so. If I can create control arrays, that would be great! Any help is appreciated!
|