Populating Combo boxes using For...Next Loop

[cutegwapings]

I'm a newly registered user of MrExcel.com, and I'm glad to be here.


I just want to know how to populate combo boxes using for...next loop.

I'm using VB in excel, and I'm getting err message "Sub or Function not defined" when I tried to run the program.

Here's my code.

Private Sub Worksheet_Activate()

Dim I As Integer

For I = 1 To 5
ComboBox(I).Clear
ComboBox(I).AddItem "N/A"
ComboBox(I).AddItem "YES"
ComboBox(I).AddItem "NO"
ComboBox(I).Text = ComboBox1.List(0)
Next I

End Sub

Thank you so much!

 

[agihcam]

Hi -
Welcome to the board.
from the code you have posted, seems you have 5 comboboxes that need to populate isn't it? Where are those comboboxes created? is it from a control toolbox?

 

[erik.van.geit]

Hello, cutegwapings
Welcome to the Board !!!!!

this works for me
note the use of
With ... End With
which avoid to "call" the comboboxes over and over again

Private Sub Worksheet_Activate()

Dim I As Integer

    For I = 1 To 5
        With Me.Shapes("ComboBox" & I).OLEFormat.Object.Object
        .Clear
        .AddItem "N/A"
        .AddItem "YES"
        .AddItem "NO"
        .Text = .List(0)
        End With
    Next I

End Sub

kind regards,
Erik

EDIT: deleted: "ComboBox1" from line ".Text = ..."

 

[erik.van.geit]

Hi -
Welcome to the board.
from the code you have posted, seems you have 5 comboboxes that need to populate isn't it? Where are those comboboxes created? is it from a control toolbox?

I'm rather sure it's controls-toolbar-comboboxes
forms-comboboxes would have different style of code

Private Sub Worksheet_Activate()

Dim I As Integer

    For I = 1 To 5
        With Me.Shapes("ComboBox" & I).OLEFormat.Object
        .List = Array("N/A", "YES", "NO")
        .Text = .List(1)
        End With
    Next I

End Sub

 

[cutegwapings]

Follow up question.

Thanks for your reply guys, I really appreciate that.

That formula works, now my problem is if the worksheet loose focus (ex: you click on the other tab) then all the scores will go back to N/A (default).

How can I prevent that? Is there any events where I can put my codes aside from "worksheet activate"?

Thanks again!

 

[cutegwapings]

And an additional question guys..

How will you use loop on this? I'm trying to populate a combobox, data coming from another worksheet.


Private Sub Worksheet_Activate()
Repnames.Clear
Repnames.AddItem Sheets("allocation").Range("B11").Value
Repnames.AddItem Sheets("allocation").Range("B12").Value
Repnames.AddItem Sheets("allocation").Range("B13").Value
Repnames.AddItem Sheets("allocation").Range("B14").Value
Repnames.AddItem Sheets("allocation").Range("B15").Value
Repnames.AddItem Sheets("allocation").Range("B16").Value
Repnames.AddItem Sheets("allocation").Range("B17").Value
Repnames.AddItem Sheets("allocation").Range("B18").Value
Repnames.AddItem Sheets("allocation").Range("B19").Value
Repnames.AddItem Sheets("allocation").Range("B20").Value
Repnames.AddItem Sheets("allocation").Range("B21").Value
Repnames.AddItem Sheets("allocation").Range("B22").Value
Repnames.AddItem Sheets("allocation").Range("B23").Value
Repnames.AddItem Sheets("allocation").Range("B24").Value
Repnames.AddItem Sheets("allocation").Range("B25").Value
Repnames.AddItem Sheets("allocation").Range("B26").Value
Repnames.AddItem Sheets("allocation").Range("B27").Value
Repnames.AddItem Sheets("allocation").Range("B28").Value
Repnames.AddItem Sheets("allocation").Range("B29").Value
Repnames.AddItem Sheets("allocation").Range("B30").Value
Repnames.AddItem Sheets("allocation").Range("B31").Value
Repnames.AddItem Sheets("allocation").Range("B32").Value
Repnames.AddItem Sheets("allocation").Range("B33").Value
Repnames.AddItem Sheets("allocation").Range("B34").Value
Repnames.AddItem Sheets("allocation").Range("B35").Value

End sub


Thanks again!!!

 

[erik.van.geit]

Re: Follow up question.

Thanks for your reply guys, I really appreciate that.

That formula works, now my problem is if the worksheet loose focus (ex: you click on the other tab) then all the scores will go back to N/A (default).

How can I prevent that? Is there any events where I can put my codes aside from "worksheet activate"?

Thanks again!

yes,
perhaps the workbook_open-event:
then replace "Me" by WorkSheets("yoursheetname")

if this doesn't suit your needs, then clarify when you want to update the boxes

 

[erik.van.geit]

And an additional question guys..

How will you use loop on this? I'm trying to populate a combobox, data coming from another worksheet.

you don't need a loop for this

ActiveSheet.ComboBox1.List() = Sheets("allocation").Range("B11:B35").Value

if you needed a loop

With repnames
.Clear
    For i = 11 to 35
    .AddItem Sheets("allocation").Cells(i, "B").Value
    Next i
End With

think about using WITH (see helpfiles !)

kind regards,
Erik

 

[cutegwapings]

Another follow question..

ActiveSheet.ComboBox1.List() = Sheets("allocation").Range("B11:B80").Value


Based from the formula above, I'm trying to populate a combobox coming from cell range B11 to B80 of the other sheet. The problem is the blank portion of the cell range appear as Zero "0" on the drop down box.

How can I remove the 0 from the list?


THanks!

 

[erik.van.geit]

you probably know how to do

If ... Then

use the loop-code which you received and add the condition

If Sheets("allocation").Cells(i, "B") <> "" Then .AddItem Sheets("allocation").Cells(i, "B")

there are slightly other ways to do this, but this is the most intuitive: as long as there aren't too much data, it will work fine

best regards,
Erik