Vba loop to populate cell range from listbox not working

kalikj

Board Regular
Joined
Sep 4, 2009
Messages
108
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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! :)
 
Upvote 0
Dear Jerry,

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

Best regards,

Jason
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top