Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo and list boxes headers in excel 2003. | Excel Programming | |||
Help with combo boxes and macros in Excel 2003 | Excel Discussion (Misc queries) | |||
MS Excel 2003 Combo Boxes | Excel Programming | |||
combo box doesn't work in excel 2003 | Excel Worksheet Functions | |||
eXceL 2003 - list or combo box question | Excel Discussion (Misc queries) |