Paste Multiple listbox items starting at ActiveCell

[Casey]

Hi,
I have a user form with a list box set to multiple selection. On the
same user form is a command button to send the selected items to the
active sheet. It works fine for the original application, however, I
need to modify the code so it will start pasting the selections staring
at the ActiveCell rather than looking for the first blank cell in a
column.

Here is my Code:

Private Sub cmdEnterSelection_Click()
Dim i As Long
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
ActiveSheet.Range("A105").End(xlUp).Offset(1, 0) _
Value = Me.ListBox1.List(i)
End If
Next i
End Sub

 

[Ardus Petus]

Private Sub cmdEnterSelection_Click()
Dim i As Long
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
ActiveCell.Value = Me.ListBox1.List(i)
ActiveCell.Offset(1, 0).Select
End If
Next i
End


HTH

 

[Doug Glancy]

Casey,

Dim i As Long
Dim j As Long
j = 0
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
ActiveCell.Offset(j, 0).Value = Me.ListBox1.List(i)
j = j + 1
End If
Next i
'if you want to select the next blank cell at end
ActiveCell.Offset(j, 0).Select

hth,

Doug

 

[Casey]

Ardus & Doug,
Thank you. I have only had a chance to test Ardus' Code but it worked
brilliantly. Doug I will test your later, I really appreciate the help
guys.
If I might ask a follow up question. I have place a RefEdit control on
the user form what kind of code would let me tie the RefEdit control to
the ActiveCell and then use the RefEdit to select a different cell while
the user form is open.

 

[Doug Glancy]

Casey,

Use the form's Initialize event:

Private Sub UserForm_Initialize()
RefEdit1.Value = ActiveCell.Address
End Sub

Then I'd change the click event to this:

Private Sub cmdEnterSelection_Click()
Dim i As Long
Dim j As Long
j = 0
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
Range(RefEdit1.Value).Offset(j, 0).Value = Me.ListBox1.List(i)
j = j + 1
End If
Next i
'if you want to select the next blank cell at end uncomment following
Range(RefEdit1.Value).Offset(j, 0).Select
End Sub

hth,

Doug

 

[Casey]

Doug,
Just the ticket, thank you. Thanks for the options as well.