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

[shivboy]

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:

Private Sub myList_Click()
Dim v1, v2, v3 As String
Dim rng As Range

Set rng = Range(myList.ListFillRange)

v1 = myList.Value
v2 = rng.Offset(myList.ListIndex, 1).Resize(1, 1).Value
v3 = rng.Offset(myList.ListIndex, 2).Resize(1, 1).Value

myForm.myTxt1.Value = v1 
myForm.myTxt2.Value = v2 
myForm.myTxt3.Value = v3 

myForm.Show
End Sub

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

Peace,

Shivboy

[Leith Ross]

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

Private Sub ListBox1_Click()

  Dim v1, v2, v3 As String
  Dim rng As Range

    Set rng = Range(ListBox1.ListFillRange)

    With ListBox1
      v1 = .Value
      v2 = rng.Offset(.ListIndex, 1).Resize(1, 1).Value
      v3 = rng.Offset(.ListIndex, 2).Resize(1, 1).Value
    End With
    
End Sub

Sincerely,
Leith Ross

[shivboy]

Hi Leith,

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

Shivboy

[Leith Ross]

Hello Shivboy,

Can you post your workbook?

Thanks,
Leith Ross

[shivboy]

Hi Leith,

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

Shivboy

[royUK]

The workbook in the zip is not a 2007 format.

I can't see why you are working like this, why not have a Listbox on the UserForm? You can't define a cell like that, try

Private Sub myList_Click()
    Dim v1, v2, v3 As String
    Dim rng    As Range
    Set rng = Sheet2.Cells(myList.ListIndex + 1, 1)

    v1 = myList.Value
    v2 = rng.Offset(0,1).Value
    v3 = rng.Offset(0,2).Value

    myForm.txt1.Value = v1
    myForm.txt2.Value = v2
    myForm.txt3.Value = v3
    myForm.Show
End Sub