Populate Userform From A Listbox

  • I have searched all day for something close and havent turned up anything my retarded self could use.
    I have 2 userforms. Userform 1 submits data to a sheet. Userform 2 displays all data on the sheet in a listbox. So far everything works as it should. What i would like to do is have a 3rd userform that userform 2 "With the listbox" can update to for editing. My 3rd userform will be identical to the first. So i would like to send data from the listbox to the userform/get edited and then resubited to the sheet.Any clue on how to accomplish this? Thanks in advance.......You Guys Rock!!!


    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Populate Userform From A Listbox

    have you checked the possible answers on the bottom

    "The problem with designing vba code completely foolproof is to underestimate the ingenuity of a complete fool."

  • Re: Populate Userform From A Listbox

    Having looked at the Workbook you uploaded here and now read this recent thread, I would like to suggest you are making life hard for yourself, and eventually your user.

    Currently you have 'UserForm3' that performs a search and then opens 'UserForm6' which contains a ListBox that you load with the results of the search from 'UserForm3'. You are now asking to open a third UserForm to use to edit that search. Rather than having multiple UserForms, I think you will find it easier to have a single larger UserForm with more function. If you don't want to show all the controls when they are not relevant or empty then you can use the Visible property to hide them.


    ListBox1.Visible = False
    ListBox1.List = varCells
    ListBox1.Visible = True

    It is easier to pass data around on a single UserForm, particularly if it is arrays of data. Often this is simpler fo the user too as they don't have multiple UserForms to select.

    So from what I have seen of your project you could have something like:


    Then you can either .Visible = False or .Enabled = False when the controls are not in use. Just something to consider.

  • Re: Populate Userform From A Listbox

    That would be ok, but how would i program that? I would still need the listbox to display all the results, then when i pressed edit the boxes would be populated.then edit/resubmit the data to thier oridginal cells. Sorry if im asking alot.

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Populate Userform From A Listbox

    This is turning into a bit of a project so somewhat beyond the scope of this part of Ozgrid I think, but let me give you some pointers:

    1) If your intention is to allow the user to search for a Description then return all the values from your table that correspond to that Description, then you don't need the ListBox. Just the individual TextBoxes for each value
    2) ListBox can be Enabled/Disabled using the Enabled property. This still shows the TextBox but the input area is 'greyed out' and can't be selected. Good for when it contains values the user can't alter.

    TextBox1.Enabled = True
    TextBox1.Enabled = False

    3) Assigning the contents of a cell to a TextBox and vice versa is simple.

    TextBox1.Value = Range("A1").Value
    Range("A1").Value = TextBox1.Value

    If you work your way through your existing code you should be able to figure out how to modify it with the above information to achieve what you want. If you get specific problems then by all means start a new thread.

    Good luck :)

  • Re: Populate Userform From A Listbox

    Thanks for the pointers Rob, i didnt mean to come off like i wanted someone to do it for me. I apologize for that. I was just seeing how others would have gone about it. Like you just did :) Thanks again

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Populate Userform From A Listbox

    Another suggestion I would make on this is that you are basically building a mini-database. It is generally very good practice in these situations to use a unique 'key' or 'ID' to refer to each entry in the database. This is generated internally by your Excel and the user does not even have to be aware of it. This ID would usually be the first column of your database table of data. Every time you add a new entry you increase the ID. If you delete an entry entirely, you don't usually re-use the key it just becomes a gap in the sequence.

    There are many reasons for using an ID, but a few important ones are:

    1) When you lookup another value in the database (eg by using Match or Find, ) you can easily retrieve its ID from the first column. Then retrieving all other values for that ID become easy using Vlookup(ID, Data_Table, Item_Column). As ID is always allocated in ascending order, Vlookup and Match is faster and more efficient to lookup using this ID than constantly using exact matches on non-sorted values.
    2) Consider a situation where while creating a new entry in the database the user makes a mistake in say Description. If the new entry, errors and all, has an ID key, then when you retrieve information for it you can still change Description without getting into issues about changing the value you have just searched for.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!