+ Reply to Thread
Results 1 to 7 of 7

vba excel - last listbox item transfer erasing all listbox items except last item

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    vba excel - last listbox item transfer erasing all listbox items except last item

    I have 2 adjacent listboxes that I transfer items back and forth between. Everything transfers properly between them from the first item down until the last item. But if I select the last item to transfer in either ListBox, the item transfers to its adjacent list, however the rest of its originating list disappears. I've attached a sample workbook so that you can see what I'm talking about. Also ListBox1 actually has more code associated with it that fills from a lookuplist, so that is why there is so much odd coding regarding the ListBox1 fill routine.
    Attached Files Attached Files
    Last edited by terriertrip; 11-03-2017 at 05:10 PM. Reason: the last item, not the second to last item

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: vba excel - last listbox item transfer erasing all listbox items except last item

    When I click the Add button on the form nothing is transferred from the first listbox to the second.

    However the selected item seems to be removed from the first listbox and a row appears to be removed from the sheet.

    So, I'm afraid I'm a little confused.

    How can we replicate the behaviour you describe?
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - last listbox item transfer erasing all listbox items except last item

    Well when I select an item from ListBox1 and click Add, the item is transferred to ListBox2. The row is removed from Sheet1 and appears on Sheet2. If I click the item in ListBox2 and click Remove, the item is transferred back to ListBox1 from ListBox2. The row is removed from Sheet2 and appears back on Sheet1.

    To replicate the behaviour, select the last item in ListBox1 and click Add. The item transfer will occur between ListBoxes and sheets, however, the entire list is removed from ListBox1 and all the rows are removed from the Sheet1.

  4. #4
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - last listbox item transfer erasing all listbox items except last item

    Is there anybody that can help me with this?

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: vba excel - last listbox item transfer erasing all listbox items except last item

    Closed at OP request to post in Commercial Services.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: vba excel - last listbox item transfer erasing all listbox items except last item

    I reopened this because I believe I have solved the problem. You have unnecessary looping logic in the Add event. The same is probably true of Remove but you can revise that yourself. When you have a single-select listbox, you can use ListIndex to determine what is selected. You do not need to loop through all items.

    I did not actually diagnose the root cause of your problem, but when I made this change the problem you described is no longer occurring. You also had nested With scopes on two listboxes, and I do not recall the rules for that; that could have been a problem too. You may have intended to reference one listbox when in fact the code was using the other.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - last listbox item transfer erasing all listbox items except last item

    Thanks 6StringJazzer. Through some dissection this weekend, I see as you've pointed out that I was using a MultiSelect loop in the removal code for the ListBox2 set to SingleSelect property. I'm not sure if the example you sent with the Add event was missing something though, as the add event only adds the first row (minus header) to ListBox2. I've attached my working example with revisions to both the Add and Remove events.

    I am having an issue with the add event code however, and I'm not sure if it's because it's the nature of MulitSelect ListBoxes. It's somewhat minor but annoying. For some reason, the Add routine is not resetting itself after it runs, even if I reset all the ListBoxes at the end of the routine. The clue that it is not resetting is that if the Add event runs after initialization, the msgbox does not show up when the Add event is run again if nothing is selected in ListBox1. The Remove event works fine which leads me to believe it's because a MultiSelect ListBox is a different animal. I've tried:
    Please Login or Register  to view this content.
    The only thing that works is Unloading and Showing the UserForm after the Add event occurs, so if I have to do that, I guess I can live with that.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA To populate listbox with duplicate item by increasing the item quantity
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-01-2016, 07:51 AM
  2. [SOLVED] if anything in listbox select first item, if listbox empty do nothing (listbox in userform
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2015, 12:49 PM
  3. Transfer random listbox items to new listbox and then loop through selected items
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-31-2013, 05:58 PM
  4. [SOLVED] Items in Userform Listbox are wider than list box. Need to see all character of item
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2012, 06:40 AM
  5. How to number listbox items, enable a cmd button and move item lines?
    By Kimberley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2011, 09:10 AM
  6. Multi select Listbox Items selection based on other Listbox item selection.
    By srinivassathi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2011, 05:53 AM
  7. Transfer Item From Range1 to Range2 Using ListBox
    By hifliers in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-22-2009, 06:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1