Hi all,

Does anyone know how to dynamically fill a list box based on the user's
previous entries? My form has several list boxes, but I would like to
set it up so that if the user makes a selection in listbox1, it alters
the available selections in the subsequent listbox2.

So, for example, listbox1 has "Eastern US" and "Western US" as
available selections. Listbox2 has all the states. If the user picks
"Eastern US", I don't want any of the states in the west to show up in
listbox2.

How can I do this ... please help. Thanks.


--
gonger
------------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...o&userid=34797
View this thread: http://www.excelforum.com/showthread...hreadid=545541

Hi,

you can use event depended code (for example Private Sub
ListBox1_Change()) to fill subsequent listbox with appropriate items,
after user picks entry in listbox1.

Regards,
Ivan

Thanks, Ivan.

Unfortunately, I'm not that savvy in vba, so I don't know how to do
what you recommended. Can you show me a small code example of what you
suggested?


--
gonger
------------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...o&userid=34797
View this thread: http://www.excelforum.com/showthread...hreadid=545541

Bob Phillips discusses this issue for dropdowns, but the same concepts would
apply to Listboxes.

http://www.xldynamic.com/source/xld.Dropdowns.html

--
Regards,
Tom Ogilvy



"gonger" wrote:


Hi all,

Does anyone know how to dynamically fill a list box based on the user's
previous entries? My form has several list boxes, but I would like to
set it up so that if the user makes a selection in listbox1, it alters
the available selections in the subsequent listbox2.

So, for example, listbox1 has "Eastern US" and "Western US" as
available selections. Listbox2 has all the states. If the user picks
"Eastern US", I don't want any of the states in the west to show up in
listbox2.

How can I do this ... please help. Thanks.


--
gonger
------------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...o&userid=34797
View this thread: http://www.excelforum.com/showthread...hreadid=545541

Thanks, Tom. It's going to get a little messy in that I'm trying to do
this in a form, but I'm going to give it a shot.


--
gonger
------------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...o&userid=34797
View this thread: http://www.excelforum.com/showthread...hreadid=545541

In it simplest form

assume listbox1 and listbox2

Private Sub Listbox1_click()
With Userform1
If .Listbox1.ListIndex = -1 then exit sub
.Listbox2.Clear
With Worksheets("Data")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
v = rng.resize(,2).Value
end with
for i = 1 to ubound(v)
if v(i,lbound(v,2)) = .Listbox1.Value then
.Listbox2.AddItem v(i,Lbound(v,2)+1)
end if
Next
End With
End sub

--
Regards,
Tom Ogilvy

"gonger" wrote:


Thanks, Tom. It's going to get a little messy in that I'm trying to do
this in a form, but I'm going to give it a shot.


--
gonger
------------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...o&userid=34797
View this thread: http://www.excelforum.com/showthread...hreadid=545541

That's very cool, Tom. I'm going to give it a shot. Thanks for th
help

--
gonge
-----------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...fo&userid=3479
View this thread: http://www.excelforum.com/showthread.php?threadid=54554