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

On 17 feb, 17:43, K wrote:
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

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

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)


"K" wrote in message
...
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

On 17 Feb, 17:21, "Rick Rothstein"
wrote:
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)

"K" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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.

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)


"K" wrote in message
...
On 17 Feb, 17:21, "Rick Rothstein"
wrote:
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)

"K" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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.

On 17 Feb, 18:11, "Rick Rothstein"
wrote:
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)

"K" wrote in message

...
On 17 Feb, 17:21, "Rick Rothstein"





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

"K" wrote in message

....

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- Hide quoted text -

- Show quoted text -

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 -

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

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?

--
Rick (MVP - Excel)

On Feb 17, 9:34*pm, "Rick Rothstein"
wrote:
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?

--
Rick (MVP - Excel)

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.

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/groups?selm...BDD4%40msn.com

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

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

--

Dave Peterson

On Feb 18, 1:10*pm, Dave Peterson wrote:
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/groups?selm...BDD4%40msn.com

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

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

--

Dave Peterson

Wow thanks Dave. You are greate