Thread: Populating a Range of Cells from a ListBox Selection

I am fairly new to VBA, so I am sure this is a fairly simple task.

I have inserted an activeX Listbox onto a sheet and want to use a single selection in the Listbox to populate a range for two rows of cells on another sheet. One row of populated cell data will remain constant regardless of the Listbox selection while the other row will change depending on the selection.

Below is what I have so far. I have the constant data row figured out, but I do not know how to code for the second row to change based on the ListBox selection. I know the listbox can put out numbers for each selection, but I am not sure how to make the output numbers correspond to different rows of data. The output range C2:AK2 would be different for each selection such as C2:AK2 for selection 1, C3:AK3 for selection 2, C4:AK4 for selection 3, etc.

Private Sub ListBox_Click()

With ListBox
Worksheets("Sheet1").Range("E10:AM10").ClearContents
Worksheets("Sheet1").Range("E11:AM11").ClearContents
Worksheets("Sheet1").Range("E10:AM10").Value = Worksheets("Sheet2").Range("C1:AK1").Value
Worksheets("Sheet1").Range("E11:AM11").Value = Worksheets("Sheet2").Range("C2:AK2").Value ---This line is what I need changed to populate different data based on the listbox selection.

End With

End Sub

Possibly by using the listbox's linkedcell property... something like...

Private Sub ListBox_Click()
    Dim lcValue As Long    'assumes linkcell hold a number

    lcValue = Range("A1").Value2 'assumes A1 is the listbox's linked cell
    Worksheets("Sheet1").Range("E10:AM11").ClearContents
    Worksheets("Sheet1").Range("E10:AM10") = Worksheets("Sheet2").Range("C1:AK1").Value
    Worksheets("Sheet1").Range("E11:AM11") = Worksheets("Sheet2").Range(Cells(2, lcValue), Cells(2, 29 + lcValue)).Value

End Sub

I tried using your code as suggested, but since I am new to VBA I am not sure why I am getting an error for the last line. I noticed that my lcValue is =0 when I hover over it. I tried changing around the cell values, but I still received the same error. Any suggestions?

The code is just an example. It assumes you've set the linked cell of the listbox = A1. It also assumes the listbox selection returns a row number. If you can't get it to work, please upload an example of your work so that we can more clearly see what you are trying to accomplish.

HTH
Rich