Problem in Returning Values from a Multi-column Listbox (ActiveX)

Hi,

I need to achieve the following:

1. insert an ActiveX listbox (single select, 4 columns) in the worksheet (Sheet1)
2. populate the listbox with a range in another sheet (sheet2)
3. after populating the list, whenever the user clicks on a row, a userform should pop-up with all the values in the 4 columns of the listbox in 4 different textboxes
4. user may want to edit the data, which may then be committed to the data sheet (sheet2) and refreshed in the listbox as well.

I am stuck at the 3rd step. Whenever I try to access the listbox range, the following error is generated:

Run-time error '1004':

Method 'Range' of object '_Worksheet' failed

The code that I am using is:

Please Login or Register  to view this content.

Is my approach wrong? Or some problem with my coding? Please help.

Peace,

Shivboy

Hello Shivboy,

I tested your code without the UserForm and it works fine. Did you set the ListFillRange property manually or by code? If it isn't set, you will get this error.

My Test Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hi Leith,

I have set the ListFillRange property manually in the Properties of the listbox. But it still isn't working!

Shivboy

Hello Shivboy,

Can you post your workbook?

Thanks,
Leith Ross

Hi Leith,

Here is the file attached. I forgot to mention one thing, I am using Excel 2007, if that makes a difference.

Shivboy

Hello Shivboy,

Sorry, I don't have 2007 on my machine. I did try to run your workbook, but with no success. Someone with 2007 needs to help you with this code problem.

Sincerely,
Leith Ross