+ Reply to Thread
Results 1 to 12 of 12

vba excel - adding listbox values from listbox

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

    vba excel - adding listbox values from listbox

    Can someone help me and see anything wrong with this code? I'm trying to add an item to a listbox from a listbox and it won't display my pricing. When I save and reopen the form, the price is there and formatted correctly. The frustrating thing is that I had this working, but I had to add columns to the worksheets for additional data. I didn't think it would affect the code seeing how the indexing hadn't changed and the listboxes only display the item and price anyways. Here is my code.

    ListBox1 (Item=Index) has Desc=0, Code=1, Price=2
    ListBox2 has Desc=0, Price=1
    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: vba excel - adding listbox values from listbox

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: vba excel - adding listbox values from listbox

    Quote Originally Posted by terriertrip View Post
    I'm trying to add an item to a listbox from a listbox and it won't display my pricing.
    Is it collective transfering contents of 'ListBox1' to 'ListBox2' by e.g. CommandButton ?
    This is indicated by the "For ... next" loop.
    I have simulated this for a "one-columned list" and for one-item transferring and all is working correctly (see attachment).
    In what layout do you want to pass data from the list to the list ?
    Attached Files Attached Files

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

    Re: vba excel - adding listbox values from listbox

    Thanks Porucha for your example. I like the transfer all in one routine. For my case it would just need to remove the item from Listbox1 when transferred. The first index is a string variable and the second is currency. When the userform is loaded again after closure, the listbox items would need to remain as they were upon form closure. That being any item transferred to Listbox2 from Listbox1 would need to remain in Listbox2, and any item transferred would need to be absent from Listbox1. Is this even possible since Listbox1 is populating from a static sheet?

    AlphaFrog: I've attached a sample with examples for you to see. Please let me know if it's not clear enough. Thank you.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: vba excel - adding listbox values from listbox

    You have to set the list count for listbox2 to 2.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: vba excel - adding listbox values from listbox

    In the userform initialization procedure, FillListBox2 is not called because there is no saved data for listbox2. That means the column count and columns width properties are not set. Either set those two properties in the Initialization procedure or in the cmdAdd_Click procedure.

    On another note, you don't have to format the price from ListBox1 when adding it to ListBox2. The price in Listbox1 is text and is already formatted.

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: vba excel - adding listbox values from listbox

    Quote Originally Posted by terriertrip View Post
    1. When the userform is loaded again after closure, the listbox items would need to remain as they were upon form closure.

    2. That being any item transferred to Listbox2 from Listbox1 would need to remain in Listbox2,

    3. and any item transferred would need to be absent from Listbox1.
    It would probably require some extra sheet (or txt file on HDD) to hold the history of the operation for list boxes.
    And what about the situation when we close the workbook and open it again, for example a couple days later.
    The history should be kept or reset ? Hard case, maybe ...

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

    Re: vba excel - adding listbox values from listbox

    Thanks AlphaFrog and mehmetcik for you help. That makes sense. I call FillListBox2 in the SetListBox2 call upon initialization (if the CountA(rng) <> 0 Then fill ListBox2 'with rng being Listbox1). I see that it is referencing sheetdata from ListBox1 when it needs to be sheetdata from ListBox2. I've corrected that, but regardless even with your corrections the formatting still did not work, but for more obvious reasons.

    My thought was that the SetListBox2 sub would format ListBox2, even if there was no list in ListBox2 transferred from ListBox1 yet. I guess it doesn't work that way if there is no sheetdata to fill ListBox2. If there is sheet data for ListBox2, then the FillListBox2 in the SetListBox2 call works correctly. I've revised the Add code and ListBox2 now formats correctly either on form initialization (with ListBox2 sheet data) or with cmdAdd_Click (with no ListBox2 sheet data present).

    The SetListBox2 sub is what is lining up to be my next requirement. It retains ListBox2 items on initialization unless empty, then ListBox2 is cleared and ready for input. The tricky part is on form initialization, since all items have moved over to ListBox2, how do I initialize ListBox1 to retain it's previous settings as Porucha has quoted. It seems that I would require an additional sheet to write a revised ListBox1 to within my Save cmd, and if upon form initialization, ListBox2 sheetdata <>0, fill from the revised ListBox1 sheetdata. If ListBox2 sheetdata = false, then fill from original ListBox1 sheet data. Is this a plausible solution?

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

    Re: vba excel - adding listbox values from listbox

    It would probably require some extra sheet (or txt file on HDD) to hold the history of the operation for list boxes.
    And what about the situation when we close the workbook and open it again, for example a couple days later.
    It seems that perhaps since ListBox1 is a list derived using a quasi-match function for all intents and purposes, that maybe writing in something that takes the matched data and moves it to a sheet that can be edited might work. That would address the potential opening and closing since it would create the ListBox1 record. The save cmd essentially transfers ListBox2 to sheet data. Could it not just do the same for ListBox1?

  10. #10
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: vba excel - adding listbox values from listbox

    Quote Originally Posted by terriertrip View Post
    The SetListBox2 sub is what is lining up to be my next requirement. It retains ListBox2 items on initialization unless empty, then ListBox2 is cleared and ready for input. The tricky part is on form initialization, since all items have moved over to ListBox2, how do I initialize ListBox1 to retain it's previous settings as Porucha has quoted. It seems that I would require an additional sheet to write a revised ListBox1 to within my Save cmd, and if upon form initialization, ListBox2 sheetdata <>0, fill from the revised ListBox1 sheetdata. If ListBox2 sheetdata = false, then fill from original ListBox1 sheet data. Is this a plausible solution?
    Instead of creating saved lists for both listboxes, you could just tag column D on the Lookup data sheet when an item is added to Listbox2 with the cmdAdd_Click procedure. Then when you re-initialize the userform and populate ListBox1, as you loop through each item to be added, check the tag in column D. If the item is tagged, add it to Listbox2 instead. For this to work, you'll need to know the row number of each item in listbox1 so that when you move the item from listbox1 to listbox2, you can also tag the item on the worksheet. So when you populate the listboxes with each item, include the item's row number in another column in the listboxes.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 10-23-2017 at 03:17 AM.

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

    Re: vba excel - adding listbox values from listbox

    That's a great idea and something I could definitely use for other code routines in my organization. There would be a slight wrinkle with this application though. The lookup data is actually a child sheet with named ranges, hardcoded to update its list from a master parent lookup data sheet. Every time the user opens the child sheet the named ranges are updated to the parent sheet, so column D would get overwritten.

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

    Re: vba excel - adding listbox values from listbox

    ...
    Thanks for all your input all. I've solved this with a bit of convoluted coding. I've included the method below. Please try and not to laugh.

    Essentially the original ListBox1 items are derived from filtered results in a rather large list. Since some of the filtered values are irrelevant once they are loaded in the form, I was having issues reconciling the ListBox1 indexes with ListBox2 indexes, making transferring listbox items back and forth after loading and/or when the userform was closed and reopened nearly impossible for me to figure out how to code.

    I use an interim sub to load the ListBox1 filtered values, save them to the ListBox1 sheet, clear ListBox1 with filtered results, and then reload ListBox1 using the sheet values. This makes ListBox1 indexes identical to ListBox2 indexes and removes the criteria used to filter.

    The next procedure is if the user saves the forms's listbox states and transfers more items (bi-directional), or saves/closes and reopens the form at a later time. I need the values to remain as they were when the form was saved and/or closed. I use an Application.CountA() function to examine just the upper parts of each listbox data sheet to populate the listbox or clear the listbox, based on the state of the listbox's counterpart . I do this primarily because if the listbox is empty of data, the header row still appears in the listbox. This confuses the user and more importantly, if any listbox transfers occur to the empty box, the value populates the last row, that being the row after the header. I can't use a listbox clear before filling, because this defeats the whole purpose of retaining the listbox items in there dynamic state.

    I'm sure there is a bit more elegant way to do this, and any suggestions are appreciated, but here is what I came up with.
    Form load:
    Please Login or Register  to view this content.
    Fill ListBoxes:
    Please Login or Register  to view this content.
    Adding and removing listbox items:
    Please Login or Register  to view this content.
    Save the listboxes:
    Please Login or Register  to view this content.

+ 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. Show dynamic values at listbox depending value selected another listbox
    By Judith_Chao in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2017, 08:30 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. [SOLVED] How do I populate a listbox with a list excluding values found in another listbox?
    By Hokiefan00 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2014, 01:47 PM
  4. Adding multiple column values using listbox
    By mayurpatil in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2014, 12:33 PM
  5. Based on listbox values, go to a URL in webbrowser containing those listbox values
    By krisspy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-08-2014, 02:05 PM
  6. Editing listbox items in Dblclick event freezes Excel unless mouse moves over listbox
    By muneebmansoor in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-28-2013, 02:21 PM
  7. Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox entry.
    By jamieswift1977 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2012, 12:18 PM

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