0

I have two list boxes.Listbox_1 displays list of food items and listbox_2 is empty. upon clicking a button the selected item from box1 should be moved to box2. i dont know where to start. Can any one help me?

Ahmed Salman Tahir
  • 1,783
  • 1
  • 17
  • 26
Vidhi
  • 89
  • 1
  • 2
  • 8
  • http://stackoverflow.com/questions/19064043/vba-listbox-copy-to-listbox this helped me in moving value from box1 to box 2. But the value one copied to box2 should not be present in box1!! – Vidhi Jan 16 '14 at 07:44

3 Answers3

1

Between line:15 and line:16 of the sample code (stackoverflow.com/questons/19064043/...),

insert this line.

   .RemoveItem(i)

　

example

Private Sub UserForm_Initialize()
    '~~> Adding Sample Data to listbox 1
    ListBox1.List = ThisWorkbook.Sheets(1).Range("A1:E3").Value
End Sub

Private Sub CommandButton1_Click()
    Dim iIndex
    Dim i As Long, j As Long, k As Long

    With ListBox1
        i = .ListIndex

        ListBox2.AddItem .List(i, 0)

        j = ListBox2.ListCount - 1

        For k = 1 To .ColumnCount - 1
            ListBox2.List(j, k) = .List(i, k)
        Next k

        .RemoveItem(i)

    End With
End Sub
Community
  • 1
  • 1
maco
  • 376
  • 1
  • 9
  • iam sorry where exactly? there are only 17 line or may be i did not count the lines properly :P – Vidhi Jan 16 '14 at 09:01
  • all i am trying to do is move value from list1 to list2. once a value is selected in list1 and added to list2 the value should not be present in list1, because it is in list2. I dont know how to use .removeitem in new to vba – Vidhi Jan 16 '14 at 09:58
  • this line means removing an item from box-1. Moving an item is achieved by deleting the original item after copying it. – maco Jan 16 '14 at 10:02
  • can you provide me an example please – Vidhi Jan 16 '14 at 10:09
  • added an example to this answer. – maco Jan 16 '14 at 11:44
1

List Boxes keeps the value with index numbers.

On CommandButtom_Click, access the index of that value using ListBox.ListIndex.

Then access the value of that index and put value in the Other List. And after adding that value in the 2nd List remove the value from 1st list using that index number.

I am just giving you a direction, I hope it would help you.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
Pallav Raj
  • 1,684
  • 3
  • 17
  • 29
  • Dim iIndex Dim i As Long, j As Long, k As Long With ListBox1 i = .ListIndex ListBox2.AddItem .List(i, 0), 0 j = ListBox2.ListCount - 1 For k = 1 To .ColumnCount - 1 ListBox2.List(j, k) = .List(i, k) Next k End With this will copy selected value from list1 to list2 but how do i delete the copied one from list1?i tried removeitem but dives undefined error – Vidhi Jan 16 '14 at 11:27
  • OK, Now you are able to Copy value from ListOne to ListTwo, I would like to suggest you, when you are accessing the selected value of ListOne, at that perticular time you have the address of that selected value i.e. from ListOne. So after storing the selected value, remove it from the ListOne. Then Copy that stored value to the ListTwo. Because after setting the Value of ListOne into ListTwo you will not be able to remove the value of ListOne.So remove the value at that time when you are accessing it after storing the value. I hope it will Help you :-) – Pallav Raj Jan 17 '14 at 04:00
  • i tried doing that by using .removeitem but it gives me unspecified error. is there any other way? – Vidhi Jan 17 '14 at 04:32
  • I have update the code of Mr. Maco, Please try that one. If you again find the error then Please make me know. And If my Answer helps you then Make it Answered, I need it. – Pallav Raj Jan 17 '14 at 04:40
1
    Ok!! I am updating the above answer of Mr. Maco.

Private Sub UserForm_Initialize()
    '~~> Adding Sample Data to listbox 1
    ListOne.List = ThisWorkbook.Sheets(1).Range("A1:A8").Value 'Here ListOne is the name of ListBox1
End Sub

Private Sub CommandButton1_Click()
    Dim iIndex
    Dim i As Long, j As Long, k As Long

    With ListBox1
        i = ListOne.ListIndex

        ListTwo.AddItem ListOne.List(i, 0) 'ListTwo is the name of ListBox2

        j = ListTwo.ListCount - 1

        For k = 1 To ListOne.ColumnCount - 1
            ListTwo.List(j, k) = .List(i, k)
        Next k

        ListOne.RemoveItem (i) ' Add here the reference Name i.e. ListOne

    End With
End Sub

I hope, this should work for you.
Pallav Raj
  • 1,684
  • 3
  • 17
  • 29
  • Please make it answered, If it helps you, or if you find any problem then must make me know. – Pallav Raj Jan 17 '14 at 04:46
  • nope i still get unspecified error :( this userform is becoming hard shell to crack!!! – Vidhi Jan 17 '14 at 04:54
  • But thanks for the reply :) this has helped me improve my skills.. still a newbi to vba – Vidhi Jan 17 '14 at 04:55
  • my row source for list box1 is a defined name and its in sheet2 – Vidhi Jan 17 '14 at 04:57
  • Can you please put your code here. Because it is running well on my Computer. If you provide code then I Can help you. Also Mention your MS Office Version. – Pallav Raj Jan 17 '14 at 04:58
  • Also Mention the place of Error. So that it could be easy to understand. – Pallav Raj Jan 17 '14 at 05:02
  • Have you also changed the name of Object from Property Window? ListBox1 of your form must be Named as Listone in Property window. VBA is not finding the Object named ListOne. So change the Name of ListBox to ListOne. – Pallav Raj Jan 17 '14 at 06:31
  • Did It worked? If not then, Please go through your Objects and it's names. – Pallav Raj Jan 17 '14 at 08:04