+ Reply to Thread
Results 1 to 4 of 4

Populating a Range of Cells from a ListBox Selection

  1. #1
    Registered User
    Join Date
    12-21-2011
    Location
    Cushing, OK
    MS-Off Ver
    Excel 2007
    Posts
    4

    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

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,255

    Re: Populating a Range of Cells from a ListBox Selection

    Possibly by using the listbox's linkedcell property... something like...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-21-2011
    Location
    Cushing, OK
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Populating a Range of Cells from a ListBox Selection

    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?

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,255

    Re: Populating a Range of Cells from a ListBox Selection

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1