Populating Combo boxes using For...Next Loop

cutegwapings

New Member
Joined
Oct 12, 2006
Messages
47
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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
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

New Member
Joined
Oct 12, 2006
Messages
47

ADVERTISEMENT

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

New Member
Joined
Oct 12, 2006
Messages
47
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
ActiveSheet.ComboBox1.List() = Sheets("allocation").Range("B11:B35").Value

if you needed a loop
Code:
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

New Member
Joined
Oct 12, 2006
Messages
47
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you probably know how to do
Code:
If ... Then
use the loop-code which you received and add the condition
Code:
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
 

Forum statistics

Threads
1,141,679
Messages
5,707,787
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top