Populate list box from a range of cells?

**samtwilliams** · 11-30-2007, 02:21 PM

How do you populate a form list box from a range of cells e.g. A1:A10?

**marcospaterson** · 11-30-2007, 02:36 PM

i find it easier to do it naming the range you want to populate.
and then you can use the following code

Please Login or Register  to view this content.

**samtwilliams** · 11-30-2007, 02:59 PM

Excellent, that has solved part one,

When a user selects Item 1 in the ComboBox I want the label next to it to read the Item Code.

The item code would be in the adjacent cell to the description.

**royUK** · 11-30-2007, 03:07 PM

Are you using the ListBox on a userform/

Why are you using a listBox and a label when a listbox can have multicolumns and so display the contents of each column?

**marcospaterson** · 11-30-2007, 03:10 PM

well... im going to show you how you could write this code, but im pretty sure you could do the same thing going a different way.

for instance, listbox1 is populated from range A1:A10, and the cells you want to display are the correspondent cell on collumn B.

Please Login or Register  to view this content.

i guess this solves your problem. i didnt try this code to see if it works properly, so if you have problems let us know.

**samtwilliams** · 11-30-2007, 03:10 PM

Hi Roy,

I have used a combo box. Would a list box be more suitable? I am trying to achieve a form whereby;

If a user selects Item1 the code for the item is auto populated onto the form. All the items are listed in sheet1 with Item Description in Column A and Item Code in Column B.

What would be the most efficient way to achieve this?

**samtwilliams** · 11-30-2007, 03:31 PM

marcospaterson;

I get a compile error when changing the value of the combobox;

Method or data member not found

**royUK** · 11-30-2007, 03:56 PM

I still don't know whether you are using a UserForm or the Forms Controls from the Forms menu. Also, you originally stated that you are using a listBox.

**samtwilliams** · 11-30-2007, 03:57 PM

Hi Roy i am using a UserForm and I was using a combox. Sorry for the confusion

**royUK** · 11-30-2007, 04:14 PM

Well I would use a listBox as originally stated. Set the Column Count to two. Then load the listbox

Please Login or Register  to view this content.

**samtwilliams** · 12-01-2007, 06:51 AM

Hi Roy,

I seem to have got a little confused and can't get this to work. I need a combo box really as I don't like the way list boxes show the data.

**samtwilliams** · 12-01-2007, 07:00 AM

I get an errorwith Label9.Value =, Method or Data member not found

Please Login or Register  to view this content.

**royUK** · 12-01-2007, 07:15 AM

Don't use the select method it is inefficient, use the .Find function.
Your error is caused because a label does not have a Value Property, but a Caption.

Please Login or Register  to view this content.

**samtwilliams** · 12-01-2007, 08:44 AM

Thanks Roy,

Only other problem now is that it will only work when my active sheet1 is selected. So if I am viewing Sheet2 when the form runs i get an error.

Method 'Range' of object '_worksheet' failed.

I have fixed it using 'Sheet1.Select' before the rng selection. Would you say thats an appropriate fix?

Many Thanks for you help
Sam Williams

**royUK** · 12-01-2007, 08:50 AM

I wouldn't select sheets unnecessarily. You need to specify the sheet in your code and when loading the combobox. Can you attach a zipped workbook example?

**samtwilliams** · 12-01-2007, 09:21 AM

I have attached the xls file

**royUK** · 12-01-2007, 10:09 AM

Here's some amended code for your UserForm

Please Login or Register  to view this content.

The prescribe button overwrites previous entries, is this correct?

**samtwilliams** · 12-01-2007, 01:23 PM

Hi roy,

Thanks for that change and yoru help.

The prescribe button is meant to over write as i have yet to develop the function that clears the information after it has been exported.

Many Thanks for your help
Credit to this forum.

Sam Williams