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

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!

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

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
'<<==========


---
Regards.
Norman


wrote in message
...
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!

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

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.

" wrote:

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!

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

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.
Norman
"Norman Jones" wrote in message
...
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
'<<==========


---
Regards.
Norman


wrote in message
...
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!

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

On May 21, 5:15*pm, "Norman Jones"
wrote:
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.
Norman"Norman Jones" wrote in message

...



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
'<<==========

---
Regards.
Norman

wrote in message
...
Hello guys,

I am very new at VBA, and just got a project that requires adding a
comboboxto a "FORM" in VBA. I have this longlistof ppl's names,
and hoping to some how organize it into a drop downlist. I think I
should either usecomboboxorlistbox. I know how to do it with a
combobox, (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 acombobox.

Does anyone know how to do it??

Thanks!- Hide quoted text -

- Show quoted text -

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

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

On May 21, 5:06*pm, JLGWhiz wrote:
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.



" wrote:
Hello guys,

*I am very new at VBA, and just got a project that requires adding a
comboboxto a "FORM" in VBA. I have this longlistof ppl's names,
and hoping to some how organize it into a drop downlist. I think I
should either usecomboboxorlistbox. I know how to do it with a
combobox, (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 acombobox.

Does anyone know how to do it??

Thanks!- Hide quoted text -

- Show quoted text -

Thanks!

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

On May 22, 9:19*am, Independent1019 wrote:
On May 21, 5:06*pm, JLGWhiz wrote:





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.

" wrote:
Hello guys,

*I am very new at VBA, and just got a project that requires adding a
comboboxto a "FORM" in VBA. I have this longlistof ppl's names,
and hoping to some how organize it into a drop downlist. I think I
should either usecomboboxorlistbox. I know how to do it with a
combobox, (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 acombobox.

Does anyone know how to do it??

Thanks!- Hide quoted text -

- Show quoted text -

Thanks!- Hide quoted text -

- Show quoted text -

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

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

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
procedu

Me.ComboBox1.List = arr

with:

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



---
Regards,
Norman

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

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.



---
Regards.
Norman