Listbox1 to Listbox2?

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

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 wrote:
>
> 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

It would be no less then 3 items.

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



Moretakitty wrote:
>
> It would be no less then 3 items.

--

Dave Peterson

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" wrote:

> 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
>
>

Hi Dave,

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

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

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!!

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.

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" wrote:

> 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
>
>

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

--
Regards,
Tom Ogilvy

"Moretakitty" <[email protected]> wrote in message
news:[email protected]...
> 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!!
>

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.

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?

--
Regards,
Tom Ogilvy

"Moretakitty" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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

<vbg>.

Tom Ogilvy wrote:
>
> Seriously, based on that article? Seems like Dave Peterson's code is what
> you need.
>
> --
> Regards,
> Tom Ogilvy
>
> "Moretakitty" <[email protected]> wrote in message
> news:[email protected]...
> > 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!!
> >

--

Dave Peterson

Did you put the listboxes on a worksheet?
http://groups.google.co.uk/groups?th...2D8E%40msn.com

or on a userform?
http://groups.google.com/groups?selm...BDD4%40msn.com

Moretakitty wrote:
>
> 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.

--

Dave Peterson

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.

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

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

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

--

Dave Peterson