Populate combobox

[SteveZmyname]

Hello
Why doesn't this populate my ActiveX combobox?

Private Sub ComboBox1_Change()
Dim row As Integer
' Make sure the RowSource property is empty
Sheet1!ComboBox1.RowSource = ""

Sheet1!ComboBox1.RowSource = "Sheet1!A1:A12"
' Add some items to the ActiveX combobox
For row = 1 To 12
Sheet1!ComboBox1.AddItem Sheets("Sheet1").Cells(row, 1)
Next row
End Sub

 

[JLGWhiz]

Change the exclamation point (!) to a period (.) after Sheet1 everywhere
except in the row source reference that is within the quote marks.

 

[FSt1]

hi
1. you are using the change event. with nothing in the combo box, nothing
changes so the code never runs.
2. Active X combo box uses the listfillrange. forms combobox uses the row
source.
3. you are attempting to use listfillrange and add item. 1 is enough, both
is redundent.
using listfillrange with worksheet activate event
Private Sub Worksheet_Activate()
' Make sure the RowSource property is empty
Sheet1.ComboBox1.ListFillRange = ""
'Sheet1.ComboBox1.ListFillRange = "A1:A12"
End Sub

using the for next loop with worksheet activate event
Private Sub Worksheet_Activate()
Dim r As Range
Set r = Range("A1:A12")
ComboBox1.Clear
' Add some items to the ActiveX combobox
For Each c In r
ComboBox1.AddItem c.Value
Next c
End Sub

you could use the workbook open event

regards
FSt1

 

[SteveZmyname]

Hi
This code didn't populate my combobox...
Private Sub Worksheet_Activate()
' Make sure the RowSource property is empty
Sheet1.ComboBox1.ListFillRange = ""
Sheet1.ComboBox1.ListFillRange = "A1:A12"
End Sub

nor did this...
'using the for next loop with worksheet activate event
Private Sub Worksheet_Activate()
Dim r As Range
Set r = Range("A1:A12")
ComboBox1.Clear
' Add some items to the ActiveX combobox
For Each c In r
ComboBox1.AddItem c.Value
Next c
End Sub

I noticed c wasn't dimmed or set to anything. I think c must be a counter?

 

[FSt1]

hi
both methods worked perfictly in my 2003 excel. not sure why it's not
working for you.
did you put the code in the the sheet that owns the combo box. it wont work
if it's in a standard module or workbook module.

regards
FST1

 

[JLGWhiz]

You are probably using the combobox from the Forms toolbar. It would work
if you used the combobox from the control toolbox and the combobox name is
in fact CombBox1.

 

[SteveZmyname]

Hi
I'm using Insert from the ribbon, then Combobox from the lower half of the
toolbox that is the ActiveX portion. The code is within sheet1 not a module.
But, it still is not populating the control.

I went so far as to open a new book and insert the control and code.

I'm using...
'using the for next loop with worksheet activate event
Private Sub Worksheet_Activate()
Dim r As Range
Set r = Range("A1:A12")
ComboBox1.Clear
' Add some items to the ActiveX combobox
For Each c In r
ComboBox1.AddItem c.Value
Next c
End Sub

thanks for your help. It's probably some small detail I'm overlooking.