Controls names as varible?

[lily]

I have a worksheet with a command button and a combo box (with 26
items). When the command button is clicked, a new combobox is created
right at the next row of the first one by copying it. When the command
button is clicked the second time, a new combobox is created right at
the next row of the second one, as so on. My code is:

Private Sub ComboBox1_Change()
If ComboBox1.Value = "A" Then frmA.Show
If ComboBox1.Value = "B" Then frmB.Show
.....
If ComboBox1.Value = "Z" Then frmZ.Show
End Sub

Private Sub ComboBox2_Change()
If ComboBox1.Value = "A" Then frmA.Show
If ComboBox1.Value = "B" Then frmB.Show
.....
If ComboBox1.Value = "Z" Then frmZ.Show
End Sub
........
........
........
Private Sub ComboBox26_Change()
If ComboBox1.Value = "A" Then frmA.Show
If ComboBox1.Value = "B" Then frmB.Show
.....
If ComboBox1.Value = "Z" Then frmZ.Show
End Sub

_My_intention_is_to_shorten_the_above_ugly_code_to:_

Private Sub ComboBox(1 to 26)_Change()
If ComboBox1.Value = "A" Then frmA.Show
If ComboBox1.Value = "B" Then frmB.Show
.....
If ComboBox1.Value = "Z" Then frmZ.Show
End Sub

Of course, it doesn't work. Please give some ideas. Thanks.

 

[Guest]

this page talks about using data validation which may be all you really need.
But it also has sample code on using a combobox to simulate data validation
and have more contron on the appearance of items in the list. Check it out.

Debra Dalgleish's site
http://www.contextures.com/xlDataVal10.html