Prevent duplicates in ListBox
 

I have two list boxes (multiselection).
I,m transferrig the selection from listbox1 into listbox2.
How can I prevent the user from transferring an item twice???

Thanks in advance

Prevent duplicates in ListBox
 

After you transfer the item, remove it from the first listbox.

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

LuisE wrote:

I have two list boxes (multiselection).
I,m transferrig the selection from listbox1 into listbox2.
How can I prevent the user from transferring an item twice???

Thanks in advance

--

Dave Peterson

Prevent duplicates in ListBox
 

thanks Dave for the prompt response.

They are in an userform. I'm not sure if I want to delete theitem from the
origin (listbox1).

There is is plenty of code to answer my question though.

Thanks again and Happy New Year

"Dave Peterson" wrote:

After you transfer the item, remove it from the first listbox.

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

LuisE wrote:

I have two list boxes (multiselection).
I,m transferrig the selection from listbox1 into listbox2.
How can I prevent the user from transferring an item twice???

Thanks in advance

--

Dave Peterson

Prevent duplicates in ListBox
 

You could always check in the second listbox to see if it item already appears
there.

Post back if you need more help.

(I like the "Movement" between listboxes. As a user, I think it makes more
sense. <vbg)

LuisE wrote:

thanks Dave for the prompt response.

They are in an userform. I'm not sure if I want to delete theitem from the
origin (listbox1).

There is is plenty of code to answer my question though.

Thanks again and Happy New Year

"Dave Peterson" wrote:

After you transfer the item, remove it from the first listbox.

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

LuisE wrote:

I have two list boxes (multiselection).
I,m transferrig the selection from listbox1 into listbox2.
How can I prevent the user from transferring an item twice???

Thanks in advance

--

Dave Peterson


--

Dave Peterson