Populate Userform From A Listbox

Re: Populate Userform From A Listbox

have you checked the possible answers on the bottom

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.

eg

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:

[ATTACH=CONFIG]33330[/ATTACH]

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

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

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.