setting up drop down combo box with VBA

[windsurferLA]

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.

 

[Guest]

I think your code is in the wrong place.
In a standard code module put this:

SUb ShowForm()
Userform1.Show
End Sub

Select the userform aclled userform1 and go to its code sheet. Add these:

Private Sub UserForm_Initialize()

with listbox1
.AddItem "A"
.Additem "B"
' ....etc
end with

End Sub


to grab data from a sheet instead, assuming your data is in a range named
"MyData"

Private Sub UserForm_Initialize()
Dim cell As Range
With ListBox1
For Each cell In Names.Item("MyData").RefersToRange
.AddItem cell.Value
Next
End With
End Sub

HTH

 

[Bob Phillips]

Windsurfer,

That's the trouble with reading a VB6 book and then trying to apply to VBA
.

In VB, when a form is loaded, it triggers the Form_Load event. But in VBA,
it triggers the Userform_Initialize event.

Try

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


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[windsurferLA]

I tried what you suggested with many variations and at first it seemed
like no matter what I did, I could not load a list box with entries.
Then, all of a sudden, it started working. I’m not sure why. Perhaps
it started working after I clicked on the list box in the form window.
The clicking action automatically opened a template macro in the
UserForm1(code) sheet. Since then I’ve learned how to read which item
in the list box was selected and to hide the box after the selection is
made.

My problem may have been that I had been concentrating on loading text
into a combo box rather than a list box. When I would run the macro,
the combo box would pop-up, but it would appear empty. It was not until
accidentally clicked on the down arrow that I realized clicking the
arrow was essential to getting the list to appear in the combo box.

Thanks for the help.

 

[Bob Phillips]

Set the Listindex property to 1

--

HTH

RP
(remove nothere from the email address if mailing direct)