Paste Multiple listbox items starting at ActiveCell

C

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
 
A

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
 
D

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
 
C

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.
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top