Function of a add button in vba userform

Question

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?

stackoverflow.com/questions/19064043/… 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
– Vidhi, Commented Jan 16, 2014 at 7:44

Pallav Raj · Accepted Answer · 2014-01-17 04:43:24Z

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.

edited Jan 17, 2014 at 4:43

answered Jan 17, 2014 at 4:37

Pallav Raj

1,7443 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Pallav Raj Over a year ago

Please make it answered, If it helps you, or if you find any problem then must make me know.

Vidhi Over a year ago

nope i still get unspecified error :( this userform is becoming hard shell to crack!!!

Vidhi Over a year ago

But thanks for the reply :) this has helped me improve my skills.. still a newbi to vba

Vidhi Over a year ago

my row source for list box1 is a defined name and its in sheet2

Pallav Raj Over a year ago

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.

|

Community · Accepted Answer · 2017-05-23 12:09:08Z

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

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Jan 16, 2014 at 8:42

maco

3761 silver badge9 bronze badges

5 Comments

Vidhi Over a year ago

iam sorry where exactly? there are only 17 line or may be i did not count the lines properly :P

Vidhi Over a year ago

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

maco Over a year ago

this line means removing an item from box-1. Moving an item is achieved by deleting the original item after copying it.

Vidhi Over a year ago

can you provide me an example please

maco Over a year ago

added an example to this answer.

Patrick Hofman · Accepted Answer · 2014-01-27 09:29:46Z

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.

edited Jan 27, 2014 at 9:29

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

answered Jan 16, 2014 at 10:01

Pallav Raj

1,7443 gold badges18 silver badges29 bronze badges

4 Comments

Vidhi Over a year ago

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

Pallav Raj Over a year ago

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

Vidhi Over a year ago

i tried doing that by using .removeitem but it gives me unspecified error. is there any other way?

Pallav Raj Over a year ago

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.

Collectives™ on Stack Overflow

Function of a add button in vba userform

3 Answers 3

8 Comments

example

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

example

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related