Listbox1 to Listbox2?

[Moretakitty]

Hi!


I am a complete newbie to VB and I am running MS Office 2000. (My VB
college class was in 1994 or something like that) I have been looking
for 3 days for complete code to do this, and I am having no luck. Even
went to the local bookstore and looked for a book... searched numberous
websites...

I started in Word and wondred if it would be easier in Excel?


I was creating Forms in Word. This excalated to someone needing a form
with a selectible list of well over 25 items. Hence I fell into the
world of VB again.

Does anyone know of any resources? I have been searching for 3
days...and I do not know enough to put pieces together of different
code.

Any assistance at all would be appreciated!

Moretakitty

 

[Dave Peterson]

If it's just one item from a list of 25 (or more), you may want to put it on a
worksheet and just use Data|validation.

Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xlDataVal01.html

 

[Moretakitty]

It would be no less then 3 items.

 

[Dave Peterson]

Excel has ListBoxes that you could add to a worksheet or to a userform.

they support multiple selections

Option Explicit
Private Sub CommandButton1_Click()
Dim varr()
Dim FoundOne As Boolean
Dim iCount As Long
Dim i As Long

FoundOne = False
iCount = 0
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
FoundOne = True
iCount = iCount + 1
ReDim Preserve varr(1 To iCount)
varr(iCount) = Me.ListBox1.List(i)
End If
Next i

If FoundOne = True Then
For i = LBound(varr) To UBound(varr)
MsgBox varr(i)
Next i
End If
End Sub

 

[gocush]

this should help you get started:

Enter you list of selectable items in, say A1:A25. Select your list.
In the Name Box (to the left of Formula Bar), type a name of your list, say
"MyList"
without the quotes.

Click on View>Toolbars>Forms to bring up a toolbar of forms.
There will be an icon for Listboxes and one for Comboboxes. I prefer the
latter.
Click and drag on the combobox icon to anywhere on you worksheet. Drag a
corner to size it. Right click on it and select Format control

In the Input box, type: MyList
In the Cell link box type B2 ( or whatever cell you want)
click Enter

Click on the Arrow on your box an select an item. The item will be place in
the link cell: B2

You can also add a macro to do something whenever a selection is made.

Hope this helps

 

[Moretakitty]

Hi Dave,

I had found listboxes, but I was having difficulty linking 2 together
and adding the data.

 

[Moretakitty]

Thanks gocush,

I really should have been more specific at the time, I ran out of time
and the carpool was ready to go

The first box I was able to create successfully, the problem I had was
link 2 listboxes together so that when 1 selection is made, it would
populate to the second listbox.

Thanks

 

[gocush]

Ok. this is a fairly common question. IF you Google for "Excel dependent
combo" you will find a couple different ways to do this. Here is one way:

http://www.mrexcel.com/archive/Controls/30062.html

I use the INDIRECT method

 

[Moretakitty]

Well I'm not looking for a different item to appear in the second box,
it would be the same item. Like the user is choosing 3 or 4 tiems from
a list of 40.

 

[Moretakitty]

Ignore my last post, I could actually do it so that when one item is
chosen, the same item appears in the other box by the list... I
understand now, thank you!!

 

[Tom Ogilvy]

Seriously, based on that article? Seems like Dave Peterson's code is what
you need.

 

[Moretakitty]

Well guess as long as I get the end result, Dave Peterson's code may
work, but it doesn't tell me how to link the 2 listboxes so that
whatever is clicked in listbox 1 moves or is copied to linkbox 2.

 

[Tom Ogilvy]

Just change
Private Sub CommandButton1_Click()
to
Private Sub Listbox1_Click()

This assumes your listboxes are from the control toolbox toolbar.

But back to the question, you are saying out of a list of 40 items, you
could transfer 3 non-contiguous items from one listbox to another using the
approach in the link provided?

 

[Dave Peterson]

Based on code from a guy named Tom (from a followup to recent post).

<vbg>.

 

[Dave Peterson]

Did you put the listboxes on a worksheet?
http://groups.google.co.uk/[email protected]

or on a userform?
http://groups.google.com/[email protected]

 

[Moretakitty]

What I am saying is that I could basically say if I choose fred in
listbox1, I could then match it up with fred in listbox2.

 

[Moretakitty]

Niether so far, I am looking for the easiest way to create a form with
the options I need.

 

[Dave Peterson]

Well, try creating one in a worksheet first. To me, that always seemed simpler.