PDA

View Full Version : Add new list items in a listbox



Len Piwowar
04-11-2006, 11:19 AM
I have code that runs each time excel is started (.xla) and allows the user to search the current sheet for any of the names listed in a userform that is stored in the .xla project. Currently I use the code below to populate the listbox:

Private Sub UserForm_Initialize()

EmpList.AddItem "John Doe"
EmpList.AddItem "S. Smith"
EmpList.AddItem "Mr. Green"
EmpList.AddItem "Big Bird"
EmpList.AddItem "Joe Who"
EmpList.AddItem "Any Body"
EmpList.AddItem "Some One"

End Sub



I would like to be able to have the user add a new name to the list. I figure I could create a add name button calling a procedure that would show a textbox the user would enter a new name. The name would then have to be appended to the bottom of the
Private Sub UserForm_Initialize() with EmpList.Additem New Name.
How would I go about editing the VBA form code on the fly? Or is it even possible?:think:

Norie
04-11-2006, 11:55 AM
Why do you need to edit the code?

Do you want the newly added name to be present when the workbook is opened the next time?

If you do you would be better off storing the names somewhere.

You could store them in a text file or worksheet and refer to that in the initialize event to populate the listbox.

You could also have code that places the new item in the stored list.

Len Piwowar
04-11-2006, 12:24 PM
I do want the new name in the list to be present the next time the listbox is initialized. I'm trying to keep all the code in the (.xla) project without the need of an exterrnal file to keep the list of names.
Thanks

Norie
04-11-2006, 12:34 PM
Len

Can't you use an external file?

You can create code from code but that can have security issues.

Check this Programming to the VBE (http://www.cpearson.com/excel/vbe.htm).