How to add items to a combo box upon start?

[chamster]

I'd like to perform a certain operation upon start-up of my workbook. Suppose that cells A1:A3 contain words alpha, beta, gamma. Suppose there's a combo box (the simpler version) and it's supposed to present alpha, beta and gamma as it's fold-out list.

Now, above that, the user sould be able to add the word "delta" in cell A4 (i.e. below the three original ones) upon which, the workbook will automagically update the fold-out list with that new item. Wether it will be updated upon entering the word or after a restart, matters little, as long as the user will not have to code him/herself.

How can that be done cleverly?
(The area of most problem for me will be how to affect the contents of the combo box. The rest is fraightening too but that that much.)

[Sky86]

chamster,

i write this code based on how i understand your problem.

put this code in module

Sub Auto_open()
Dim r As Integer
r = 1

Do While Cells(r, 1).Value <> ""
Sheet1.cbo.AddItem Cells(r, 1).Value
r = r + 1
Loop

End Sub

[chamster]

1. How do you define Sheet1?
My guess is that it's the same as Worksheets (1) or equivalent.

2. How do you define cbo?
I have no idea how to obtain this object. I suspect it's the combo box, though. I've tried
Debug.Print Worksheets(1).listobjects.count
but it only gave me zero so i don't think it's right...

[johnske]

Sheet1 is the code name, and is the preferred usage for referencing sheets - see here (http://vbaexpress.com/forum/showthread.php?t=9771).

Auto_open is not meant to be used now, it's really only there for backwards compatibility and has been replaced by the Workbook_Open event in the ThisWorkbook code module.

[Sky86]

chamster,

i write this code based on how i understand your problem.

put this code in module

Sub Auto_open()
Dim r As Integer
r = 1

Do While Cells(r, 1).Value <> ""
Sheet1.cbo.AddItem Cells(r, 1).Value
r = r + 1
Loop

End Sub



Soli for the late reply, i can't access to internet on this few day.
about the code, the 'Sheet1' is the code name of the sheets.

the 'cbo' is the combobox name. No need to define, juz change it in the properties.

i using do while to loop the cells. the coding will check from cells(A1) then continue down to cells(A2) until the empty cells.
The coding will add the value in the cells into the combobox.

[anandbohra]

hi

Wether it will be updated upon entering the word or after a restart, matters little, as long as the user will not have to code him/herself.


as u said that restart is not a matter then also why to restart after each & every entry u can use this method (no use of VBA)

see attached file

[lucas]

try this: see attached look at defined name for insight.