In Excel 97 under WinXP pro, I’m trying to create a combo box where a
user can select from a set of options or input a custom entry when he
adds a record to an spreadsheet set up as a data base. The location and
output of the dialog box must move down with each newly added row.

Using the “forms” toolbar, I have created drop down lists from which the
user could select options, but the canned dialog box tools do not seem
to allow for automatically repositioning the dialog box with the
addition of each new record. Furthermore, both the list box and the
combo box do not seem allow a custom entry; they both return just the
number of the row of the selected entry.

I’ve also tried using Visual Basic to create a list box. Starting in
the VBA editor, I create a form (UserForm1) and a ListBox (ListBox1)
within the user form. I then tried running the following macro and
numerous variations on the theme based in the example given in Peter
Wright’s “Beginning Visual Basic 6.” The VBA editor seems to recognize
the code as it automatically corrects the spelling and capitalization of
the various terms, but when I run it (press F5), an empty dialog box
appears on the spread sheet. The macro code looks like.

Private Sub Form_Load()
ListBox1.AddItem “test1”
ListBox1.AddItem “test2”
End Sub

Can someone point me to more information on the subject.