Looping Through ComboBoxes

[qaxooti]

I am writing a program that will populate my combo boxes in my excel project (they are located on a UserForm). Here is some pseudo-code that explains what I want to do:

Dim i as Integer
i = 1

Open FileName For Input Access Read Shared As #FileNo
Do Until EOF(FileNo)
Line Input #FileNo, LineText
ComboBox(i).AddItem = LineText
i = i + 1
Loop
Close #FileNo

i want to iterate through all my ComboBoxes (ComboBox1, ComboBox2, etc.) and populate them with each line of the .txt file. Right now, I am having trouble doing this. I know how to do this using control arrays in VB, but I don't think you can do that in Excel, so am open for any suggestions. Help would be greatly appreciated.

 

[al_b_cnu]

Hi,

This seems to work:

Private Sub UserForm_Initialize()
Dim i As Integer, FileNo As Integer
Dim Ctl As Control
Dim LineText As String
Const FileName As String = "c:\temp\Test.txt"

FileNo = FreeFile()
i = 1

Open FileName For Input Access Read Shared As #FileNo
Do Until EOF(FileNo)
    Line Input #FileNo, LineText
    For Each Ctl In Me.Controls
        If TypeName(Ctl) = "ComboBox" Then
            Ctl.AddItem LineText
        End If
    Next Ctl
    i = i + 1
Loop
Close #FileNo

End Sub

 

[Andrew Poulsom]

You can also use:

Me.Controls("ComboBox" & i).AddItem LineText