Paste Multiple listbox items starting at ActiveCell

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
--
AP

"Casey" <Casey.25y7pn_1144504502.1588@excelforum-nospam.com> a écrit dans le
message de news:Casey.25y7pn_1144504502.1588@excelforum-nospam.com...
>
> 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
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=531178
>

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" <Casey.25y7pn_1144504502.1588@excelforum-nospam.com> wrote in
message news:Casey.25y7pn_1144504502.1588@excelforum-nospam.com...
>
> 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
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile:
> http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=531178
>

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" <Casey.25yckm_1144510801.6216@excelforum-nospam.com> wrote in
message news:Casey.25yckm_1144510801.6216@excelforum-nospam.com...
>
> 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.
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile:
> http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=531178
>