Two List Boxes Item

K

K

Hi all, I have two List Boxes on my Sheet. How can I add ListBox1
selected item to ListBox2 by macro. Please note that ListBox1 Multi
Select property is equal to fmMultiSelectExtended
 
R

RadarEye

Hi all,  I have two List Boxes on my Sheet.  How can I add ListBox1
selected item to ListBox2 by macro.  Please note that ListBox1 Multi
Select property is equal to fmMultiSelectExtended
Click to expand...

Hi K,

In Excel 2003 I have created this, connected to a button.

Private Sub cmdMove_Click()
Dim i As Integer
Dim j As Integer
Dim b As Boolean

' optional
ListBox2.Clear
' end optional

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
b = True
' check if item exists in listbox2
For j = 0 To Me.ListBox2.ListCount - 1
If ListBox1.Column(0, i) = ListBox2.Column(0, j) Then
b = False
Exit For
End If
Next
If b Then
ListBox2.AddItem ListBox1.Column(0, i)
End If
End If
Next

' optional
' remove selected items from listbox 1
For i = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) Then
ListBox1.RemoveItem (i)
End If
Next
' end optional
End Sub

HTH

Wouter
 
R

Rick Rothstein

Exactly how do you want this to function? I mean by that, what kicks off the
transfer... A button click? Double clicking the entry in the list? Something
else? Also, do you want the items removed from the first ListBox when the
are added to the second ListBox?
 
K

K

Exactly how do you want this to function? I mean by that, what kicks off the
transfer... A button click? Double clicking the entry in the list? Something
else? Also, do you want the items removed from the first ListBox when the
are added to the second ListBox?

--
Rick (MVP - Excel)






- Show quoted text -
Click to expand...

Thanks for replying Rick. I want this to be transfered on click of a
button and dont want items to be removed from first List Box. but if
you let me know that as well that items get removed from first list
box then it will be very kind of you.
 
R

Rick Rothstein

This code should do what you want (change my example worksheet name to your
worksheet's actual name)...

Dim X As Long
With Worksheets("Sheet1")
For X = 0 To .ListBox1.ListCount - 1
If .ListBox1.Selected(X) Then
.ListBox2.AddItem .ListBox1.List(X)
End If
Next
End With

There is a RemoveItem method that can be used to remove items from a list.

--
Rick (MVP - Excel)


Exactly how do you want this to function? I mean by that, what kicks off
the
transfer... A button click? Double clicking the entry in the list?
Something
else? Also, do you want the items removed from the first ListBox when the
are added to the second ListBox?

--
Rick (MVP - Excel)






- Show quoted text -
Click to expand...

Thanks for replying Rick. I want this to be transfered on click of a
button and dont want items to be removed from first List Box. but if
you let me know that as well that items get removed from first list
box then it will be very kind of you.
 
K

K

This code should do what you want (change my example worksheet name to your
worksheet's actual name)...

  Dim X As Long
  With Worksheets("Sheet1")
    For X = 0 To .ListBox1.ListCount - 1
      If .ListBox1.Selected(X) Then
        .ListBox2.AddItem .ListBox1.List(X)
      End If
    Next
  End With

There is a RemoveItem method that can be used to remove items from a list..

--
Rick (MVP - Excel)





Thanks for replying Rick.  I want this to be transfered on click of a
button and dont want items to be removed from first List Box.  but if
you let me know that as well that items get removed from first list
box then it will be very kind of you.- Hide quoted text -

- Show quoted text -
Click to expand...

Rick can you please let me know the macro for RemoveItems.
 
R

Rick Rothstein

Rick can you please let me know the macro for RemoveItems.

Removed from which list? If the first list, how did you load the items into
the list... did you use the ListFillRange property to do it or some other
way? If you used the ListFillRange, what did you put in for that property?
 
K

K

Removed from which list? If the first list, how did you load the items into
the list... did you use the ListFillRange property to do it or some other
way? If you used the ListFillRange, what did you put in for that property?
Click to expand...

I am getting items in List box by click of button. I have macro which
get all the files from specific folder and list them in list box. I
have another List box and i want to transfer items from list box 1 to
list box 2 by click of a button and i want items to be removed from
list box 1 the one i transfered to list box 2.
 
D

Dave Peterson

Saved from a previous post:

Saved from a previous post. It may give you an idea you can use...

Are these controls on a UserForm on directly on a worksheet?

If it's on a userform, then you can read this:
http://groups.google.com/[email protected]

If they're on a worksheet, then I used controls from the Control Toolbox
toolbar. I put 4 commandbuttons and two listboxes on the worksheet. Then I
named the Commandbuttons:

BTN_moveAllLeft
BTN_moveAllRight
BTN_MoveSelectedLeft
BTN_MoveSelectedRight

Right click on the worksheet tab and select view code. Paste this in:

Option Explicit
Private Sub BTN_moveAllLeft_Click()

Dim iCtr As Long

For iCtr = 0 To Me.ListBox2.ListCount - 1
Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
Next iCtr

Me.ListBox2.Clear
End Sub
Private Sub BTN_moveAllRight_Click()

Dim iCtr As Long

For iCtr = 0 To Me.ListBox1.ListCount - 1
Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
Next iCtr

Me.ListBox1.Clear
End Sub
Private Sub BTN_MoveSelectedLeft_Click()

Dim iCtr As Long

For iCtr = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(iCtr) = True Then
Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
End If
Next iCtr

For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1
If Me.ListBox2.Selected(iCtr) = True Then
Me.ListBox2.RemoveItem iCtr
End If
Next iCtr

End Sub
Private Sub BTN_MoveSelectedRight_Click()

Dim iCtr As Long

For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
End If
Next iCtr

For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox1.RemoveItem iCtr
End If
Next iCtr

End Sub
Private Sub Worksheet_Activate()

Dim myCell As Range

Me.ListBox1.Clear
Me.ListBox2.Clear
With Me.ListBox1

.LinkedCell = ""
.ListFillRange = ""

For Each myCell In Me.Range("g7:g19").Cells
If Trim(myCell) <> "" Then
.AddItem myCell.Value
End If
Next myCell

End With

Me.ListBox1.MultiSelect = fmMultiSelectMulti
Me.ListBox2.MultiSelect = fmMultiSelectMulti

End Sub

The bad news is I wasn't sure when to populate the listbox. I chose to do it
when you activated the worksheet. I don't think you'd want this--if you click
off the sheet and come back, then the listboxes are reset.

Maybe have it populated when the workbook opens????
 
K

K

Saved from a previous post:

Saved from a previous post.  It may give you an idea you can use...

Are these controls on a UserForm on directly on a worksheet?

If it's on a userform, then you can read this:http://groups.google.com/[email protected]

If they're on a worksheet, then I used controls from the Control Toolbox
toolbar.  I put 4 commandbuttons and two listboxes on the worksheet.  Then I
named the Commandbuttons:

BTN_moveAllLeft
BTN_moveAllRight
BTN_MoveSelectedLeft
BTN_MoveSelectedRight

Right click on the worksheet tab and select view code.  Paste this in:

Option Explicit
Private Sub BTN_moveAllLeft_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox2.ListCount - 1
        Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
    Next iCtr

    Me.ListBox2.Clear
End Sub
Private Sub BTN_moveAllRight_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox1.ListCount - 1
        Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
    Next iCtr

    Me.ListBox1.Clear
End Sub
Private Sub BTN_MoveSelectedLeft_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(iCtr) = True Then
            Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
        End If
    Next iCtr

    For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1
        If Me.ListBox2.Selected(iCtr) = True Then
            Me.ListBox2.RemoveItem iCtr
        End If
    Next iCtr

End Sub
Private Sub BTN_MoveSelectedRight_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
        End If
    Next iCtr

    For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox1.RemoveItem iCtr
        End If
    Next iCtr

End Sub
Private Sub Worksheet_Activate()

    Dim myCell As Range

    Me.ListBox1.Clear
    Me.ListBox2.Clear
    With Me.ListBox1

        .LinkedCell = ""
        .ListFillRange = ""

        For Each myCell In Me.Range("g7:g19").Cells
            If Trim(myCell) <> "" Then
                .AddItem myCell.Value
            End If
        Next myCell

    End With

    Me.ListBox1.MultiSelect = fmMultiSelectMulti
    Me.ListBox2.MultiSelect = fmMultiSelectMulti

End Sub

The bad news is I wasn't sure when to populate the listbox.  I chose todo it
when you activated the worksheet.  I don't think you'd want this--if you click
off the sheet and come back, then the listboxes are reset.

Maybe have it populated when the workbook opens????
Click to expand...

Wow thanks Dave. You are greate
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Move data between ListBoxes 2
Listbox 1
Sorting a listbox when items are added 4
Problem with multiselect listbox items selection 2
list box remove item by combobox 4
Prevent duplicates in ListBox 3
Stopping duplications been entered into a listbox 8
Alert if value is already in listbox 2

Top