PDA

View Full Version : Transfer items from list to textbox



elsteshepard
04-24-2009, 12:17 AM
Hi,
I've tried to find a solution for this online and on these forums, I know it must exist, because i've seen it in action. I am not an expert, in fact i know very basic vba - enough to design a userform.

I have a userform with a listbox - this contains a list of projects (lots)

I would like the user to be able to select/deselect project(s)

The way i've seen this done before is having to boxes, one with the list, the other blank, and two buttons (>) and (<) one moves an item to the right, the other moves it back again.

Can anyone help me with the code for this? (the code would be associated with the command buttons (>) and (<) I presume.)

any ideas - or any other ways of doing the same thing?

Thanks

mdmackillop
04-24-2009, 12:53 AM
Private Sub UserForm_Initialize()
ListBox1.List() = Range("data").Value
End Sub

Private Sub CommandButton1_Click()
ListBox2.AddItem ListBox1
ListBox1.RemoveItem ListBox1.ListIndex
End Sub

Private Sub CommandButton2_Click()
ListBox1.AddItem ListBox2
ListBox2.RemoveItem ListBox2.ListIndex
End Sub

elsteshepard
04-24-2009, 03:20 AM
Thanks for the code

i'm getting an error

permission denied
highlighted in code ListBox1.AddItem ListBox2

What does this code do?
ListBox1.List() = Range("data").Value

mdmackillop
04-24-2009, 03:33 AM
I used that line to fill listbox1 with data from a named range called "Data". Replace this with your own code for populating the listbox.

elsteshepard
04-24-2009, 03:42 AM
Does it matter how I fill the listbox to the way the code works with the command buttons?

I am using the named range method, by naming the Rowsource field in the listbox setup

Might this be the cause of the error?

EDIT

woohoo - I filled the listbox using the code in the userform initialize instead and it now works

One other requirement - when i move back, i.e. deselect - the entry goes to the bottom of the list
Is it possible to put it back where it came from. The list is sorted numericalally.

mdmackillop
04-24-2009, 03:56 AM
You would need to sort the list and reload it. Do a seach for BubbleSort (no time just now)

mdmackillop
04-24-2009, 10:07 AM
Dim arr() As Variant
Private Sub UserForm_Initialize()
ListBox1.List() = Range("data").Value
End Sub

Private Sub CommandButton1_Click()
ListBox2.AddItem ListBox1
If ListBox2.ListCount > 1 Then
ReDim arr(ListBox2.ListCount - 1)
For i = 0 To ListBox2.ListCount - 1
arr(i) = ListBox2.List(i)
Next
BubbleSort arr
ListBox2.List() = arr
End If
ListBox1.RemoveItem ListBox1.ListIndex
End Sub

Private Sub CommandButton2_Click()
ListBox1.AddItem ListBox2
If ListBox1.ListCount > 1 Then
ReDim arr(ListBox1.ListCount - 1)
For i = 0 To ListBox1.ListCount - 1
arr(i) = ListBox1.List(i)
Next
BubbleSort arr
ListBox1.List() = arr
End If
ListBox2.RemoveItem ListBox2.ListIndex
End Sub

Sub BubbleSort(MyArray() As Variant)
Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim List As String
First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i
For i = 1 To UBound(MyArray)
List = List & vbCrLf & MyArray(i)
Next
End Sub