Results 1 to 6 of 6

Vba loop to populate cell range from listbox not working

This is a discussion on Vba loop to populate cell range from listbox not working within the Excel Questions forums, part of the Question Forums category; Can anybody help me with the following problem. I am trying to use VBA code to take the relevant selections ...

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    29

    Smile Vba loop to populate cell range from listbox not working

    Can anybody help me with the following problem. I am trying to use VBA code to take the relevant selections made to populate a Listbox and to insert them back into a cell range within the worksheet. The code I have come up with so far is:

    Private Sub cmdApply_Click()

    Dim i As Integer
    Dim j As Integer
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim StartCol As Integer
    Dim EndCol As Integer
    Dim temp

    StartRow = 7
    EndRow = 28
    StartCol = 19
    EndCol = 19

    For i = StartRow To EndRow
    For j = StartCol To EndCol

    For n = 0 To (lstSelectn.ListCount - 1)
    If lstSelectn.List(n) <> "" Then
    temp = lstSelectn.List(n)
    Cells(i, j).Value = temp
    End If

    Next i
    Next j

    Next

    End Sub

    The code should take any data within the listbox and insert the individual text into subsequent cells across the specified cell range. However, I keep getting various error messages. I have tried permutations of the code which do run but which copy a single entry from the listbox across the whole cell range! Any help is much appreciated!

  2. #2
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    7,044

    Default Re: Vba loop to populate cell range from listbox not working

    Hi kalikj,

    It sounds like you have a ListBox that allows Multiple Selections
    and you want to place all selected items in your worksheet beginning at Cell S7.
    Is that correct?

    If so, much of the looping that you have can be eliminated.

    Try...
    Code:
    Private Sub cmdApply_Click()
        Dim cStartCell As Range
        Dim n As Long, lRow As Long
        Set cStartCell = Sheets("Data").Range("S7")
    
        With Me.lstSelectn
            For n = 0 To .ListCount - 1
                If .Selected(n) Then
                    lRow = lRow + 1
                    cStartCell(lRow) = .List(n)
                    .Selected(n) = False 'option to clear
                End If
            Next n
        End With
    End Sub
    Last edited by Jerry Sullivan; Apr 21st, 2012 at 04:35 AM.
    Using Excel 2013

  3. #3
    New Member
    Join Date
    Sep 2009
    Posts
    29

    Smile Re: Vba loop to populate cell range from listbox not working

    Dear Jerry,

    Thank you for your input, much appreciated! Your interpretation is absolutely correct. A form is loaded with tow list boxes - selections are made using the first list box and a command button clicked to transfer those selections to the second listbox (lstSelectn). On pressing the "Apply" command button this then runs the macro that I have asked for help with which needs to take those active selections within the "lstSelectn" listbox and drop them into a cell range starting with cell "S7".

    The code is looping through correctly, in that I can see the intended cell range higlighted as the macro runs - the only issue is that it is not dropping the selections from the listbox (lstSelectn) into the cell range. The macro runs, but leaves the cell range (from "S7") blank.

    Can you please help me resolve this final issue?

    Best regards,

    Jason

  4. #4
    New Member
    Join Date
    Sep 2009
    Posts
    29

    Smile Re: Vba loop to populate cell range from listbox not working

    Dear Jerry,

    Not as elegant as your code, but I have found that the "blank cell" issue can be overcome with the follwoing modification to your original:

    Private Sub cmdApply_Click()

    Dim cStartCell As Range
    Dim n As Long, lRow As Long
    Set cStartCell = Sheets("Tgt. Geog INPUT").Range("S7")
    With Me.lstSelectn
    For n = 0 To .ListCount - 1
    If lstSelectn.List(n) <> "" Then
    temp = lstSelectn.List(n)


    lRow = lRow + 1
    cStartCell(lRow).Value = temp
    .Selected(n) = False 'option to clear
    End If
    Next n
    End With

    End Sub

    A little bit clunky and I am sure there is a more elegant way, but the modification successfully drops the contents of the listbox "lstSelectn" into the intended cell range.

    Thank you once again for your help!

    Best regards,

    Jason

  5. #5
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    7,044

    Default Re: Vba loop to populate cell range from listbox not working

    Hi Jason,

    I believe the reason for the differences in results were because in misinterpreted that you were only trying to transfer "Selected" (Highlighted) items from lstSelectn to your worksheet. The code I suggested transferred nothing, because you didn't have any items highlighted.

    Now I understand that you wanted to transfer all the items in lstSelectn to the Worksheet and you used the term "Selected" because lstSelectn was a subset of items selected from your first ListBox.

    Good job on improvising and figuring out a solution!
    Using Excel 2013

  6. #6
    New Member
    Join Date
    Sep 2009
    Posts
    29

    Smile Re: Vba loop to populate cell range from listbox not working

    Dear Jerry,

    You have been a big help, and once again a big thank you.

    Best regards,

    Jason

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com