How to add an array to a combo box with Excel 2003?

[li.yuanyuan.li]

Hello guys,

I am very new at VBA, and just got a project that requires adding a
combo box to a "FORM" in VBA. I have this long list of ppl's names,
and hoping to some how organize it into a drop down list. I think I
should either use combo box or list box. I know how to do it with a
combo box, (when you have a few items to add), but I have 65 names, I
do not want to add it one by one , it is going to take too long and
sounds very tedious. I tried to search in the help, but did not find
any example with an arry added to a combo box.

Does anyone know how to do it??

Thanks!

 

[Norman Jones]

Hi Li,

In the Userform's module, try
something like:

'==========>>
Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim arr As Variant

Set WB = Workbooks("Book2")
Set SH = WB.Sheets("Sheet4")
Set rng = SH.Range("A1:A100")

arr = rng.Value

Me.ComboBox1.List = arr
End Sub
'<<==========

 

[JLGWhiz]

If you have the items in a column, you can use RowSource or List properties
to add them. You can also set up a For ... Next loop and load them with
AddItem. Check these topics in the VBA help file, or provide more specifics
about your file if you want some code recommendations.

 

[Norman Jones]

Hi Li.

My code was intended as:

'==========>>
Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim arr As Variant

Set WB = Workbooks("myBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet4") '<<==== CHANGE
Set rng = SH.Range("A1:A100") '<<==== CHANGE

arr = rng.Value
Me.ComboBox1.List = arr

End Sub
'<<==========

 

[Independent1019]

Hi Li.

My code was intended as:

'==========>>
Private Sub UserForm_Initialize()
    Dim WB As Workbook
    Dim SH As Worksheet
    Dim rng As Range
    Dim arr As Variant

    Set WB = Workbooks("myBook.xls")   '<<==== CHANGE
    Set SH = WB.Sheets("Sheet4")             '<<==== CHANGE
   Set rng = SH.Range("A1:A100")            '<<==== CHANGE

    arr = rng.Value
    Me.ComboBox1.List= arr

End Sub
'<<==========

---
Regards.










- Show quoted text -

Thank you guys! I am going to try it right now!!

 

[Independent1019]

If you have the items in a column, you can use RowSource orListproperties
to add them.  You can also set up a For ... Next loop and load them with
AddItem.  Check these topics in the VBA help file, or provide more specifics
about your file if you want some code recommendations.








- Show quoted text -

Thanks!

 

[Independent1019]

Thanks!- Hide quoted text -

- Show quoted text -

hwo come I do not see the items appear on the drop down list??

 

[Norman Jones]

Hi Independe1019,

hwo come I do not see the items appear on the drop down list??

If no data is being loaded into the ComboBox,
check that you have amended:

- the workbook name

- the worksheet name

- the range address

This data should be amende in the following
assignments in my suggested code:

============
Set WB = Workbooks("myBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet4") '<<==== CHANGE
Set rng = SH.Range("A1:A100") '<<==== CHANGE
============

If you have updated these assignment correctly
to reflect your scenario, and the specified data
range is populated, there is no reason for the
ComboBox to be loaded with your data.

Perhaps, however, your response is a reference
to the fact that, as written, no value will be
displayed in the ComboBox until the dropdown
arrow is clicked. If this is indeed your concern,
try replacing the last line of the Userform_Initialze
procedure:

Me.ComboBox1.List = arr

with:

With Me.ComboBox1
.List = arr
.ListIndex = 0
End With

 

[Norman Jones]

Hi Independent1019,

I hope that the contextindicated that:

=============
If you have updated these assignment correctly
to reflect your scenario, and the specified data
range is populated, there is no reason for the
ComboBox to be loaded with your data.
=============

was intended, conversely, to read:

If you have updated these assignment correctly
to reflect your scenario, and the specified data
range is populated, there is no reason for the
ComboBox not to be loaded with your data.