+ Reply to Thread
Results 1 to 5 of 5

Allow users to edit or add to previously entered Listbox selections in UserForm

  1. #1
    Registered User
    Join Date
    09-03-2014
    Location
    Columbus, OH
    MS-Off Ver
    Office 2007
    Posts
    7

    Allow users to edit or add to previously entered Listbox selections in UserForm

    I'm stumped on this one. I've tried every method of keyword search I can on Google and all of the Excel boards I know and I'm sure this must have been asked before but I can't figure out the right combo of search terms to find it!

    I have a User Form with a multi-select ListBox populated by a named range that works just fine for users to make selections then write the selections to a row in a sheet. Now I want the users to be able to open a User Form (or the same User Form, if that's better) to EDIT the item's they've selected. I can't figure out how to get the Listbox to show which items have already been selected!

    Here's how the spreadsheet works now:
    The main data is in a spreadsheet/table called Project Tracker. The users enter a main team owner (e.g. "Development Owner") then choose "yes" in the next column to indicate whether or not there are sub-team members. If they choose "yes", it opens a User Form for the Development team with a list of team members that can be added. The User Form shows the project ID, project name, and development owner for the row that launched the User Form. Once the user makes their name selections, the form writes to a DIFFERENT spreadsheet ("Sub Team Members"), uniquely identifying each row by project ID and team (so there should only ever be 3 possible entries for each project ID - one each for Design, Development, and Delivery). I then concatenate the entire list of names and display it (using Index/Match) in the main spreadsheet for reference.
    I want to allow the user to click on the concatenated reference list of names in the main spreadsheet to open the new/existing User Form (whichever is better) and see the existing selections to change them or add to them.

    I'm not sure which elements of my code to post for fear of putting extraneous info here that isn't relevant but I've attached a stripped down version to show what I'm doing.
    Project Calendar for Review.xlsm

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    I hope I was clear enough this time to grab someone's attention. I try to post as infrequently as possible and figure things out myself, but no amount of searching or trial-and-error seems to be working this time!

    Thanks all!
    Lori

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

    Re: Allow users to edit or add to previously entered Listbox selections in UserForm

    When the userform is initialized (Design_SubTeam in this example), this will select the names in the list from the concatenated names in column H. I assume you know how to trigger the userform when the column H cell is selected.

    Please Login or Register  to view this content.
    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
    Registered User
    Join Date
    09-03-2014
    Location
    Columbus, OH
    MS-Off Ver
    Office 2007
    Posts
    7

    Re: Allow users to edit or add to previously entered Listbox selections in UserForm

    AlphaFrog, I bow to both your brilliance and efficiency! For populating the existing selections, this is perfection! Do you recommend I create a separate button to save the "Edit" or try to configure the existing "Save" button to update the entries in the existing rows of names?

    Using the concatenated values is excellent, but I still need to have the sub team sheet with the names listed column-by-column because it will be used to track time/activity for each of the sub team members. I BELIEVE I know how to associate the edited row with the appropriate existing row and, if indeed I do, I suppose I just need to clear that row of sub team names and repopulate it with the new names... Now to see if I actually DO know how to look up the row in the Sub Team Members list based on the UserForm values! Any tips there are also welcomed! :-)
    Last edited by lorikgator; 09-23-2015 at 03:23 PM. Reason: Added a question about the solution

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

    Re: Allow users to edit or add to previously entered Listbox selections in UserForm

    You're welcome. It's essentially the same code you found.

    I suggest you configure the existing Save button because the user should only have one choice; Edit if they clicked on column H, or Save a new entry if they clicked column G. The button should adapt depending how the userform was triggered. Keep in mind though, I don't fully appreciate what you're doing and how you want to do it.

  5. #5
    Registered User
    Join Date
    09-03-2014
    Location
    Columbus, OH
    MS-Off Ver
    Office 2007
    Posts
    7

    Re: Allow users to edit or add to previously entered Listbox selections in UserForm

    I agree it seems clearer just to reuse the "Save" button...

    I think I finally figured out the rest of it, too! The "look up, clear, then replace" that I needed for my Sub Team Member spreadsheet:

    Please Login or Register  to view this content.
    I can't thank you enough, AlphaFrog! This was both tremendously helpful in moving me forward and a GREAT learning experience! 1,000 good Samaritan points for you today! :-)

+ 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. Replies: 2
    Last Post: 09-04-2015, 01:59 PM
  2. [SOLVED] Edit Data Previously Entered Through Userform
    By cmoore24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2015, 10:45 AM
  3. [SOLVED] Information from userform overwriting data previously entered by same userform
    By ciresuark in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-20-2015, 02:10 PM
  4. Populating VBA userform and Fetch previously entered data edit and make new entry
    By vijaynadiad in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-07-2013, 11:59 AM
  5. Open a excel userform with values previously entered by user using VBA
    By anuraag1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-17-2013, 07:02 AM
  6. [SOLVED] Data previously entered on UserForm dissappears, but still stored on worksheet - Excel VBA
    By eemiller1997 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-19-2012, 12:24 PM
  7. Userform ListBox that recalls prior selections made
    By skysurfer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-08-2010, 02:54 PM

Tags for this Thread

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