K
kurb
If you're allowing multiple selections in the listbox, you may not want to
display the selected items in a textbox. Maybe a second listbox would work ok.
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????
display the selected items in a textbox. Maybe a second listbox would work ok.
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????