How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

Sub DropButton_Click()
Dim i As Long, j As Long
j = 72 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

--

Dave Peterson

Thanks Dave. I thought I had tried that before and it didn't work. Guess not.
Works perfectly. Is there any way to use a named range TEAMLISTTOP to
designate the starting cell? Thanks again.
Jim T


"Dave Peterson" wrote:

Sub DropButton_Click()
Dim i As Long, j As Long
j = 72 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

--

Dave Peterson

teamlisttop is a range on the activesheet?

Sub DropButton_Click()
Dim i As Long, j As Long
j = range("teamlisttop").row - 1 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

Thanks Dave. I thought I had tried that before and it didn't work. Guess not.
Works perfectly. Is there any way to use a named range TEAMLISTTOP to
designate the starting cell? Thanks again.
Jim T

"Dave Peterson" wrote:

Sub DropButton_Click()
Dim i As Long, j As Long
j = 72 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

--

Dave Peterson


--

Dave Peterson

Sorry I didn't make that clearer. Yes - TeamData!A61:A70.
--
Jim T


"Dave Peterson" wrote:

teamlisttop is a range on the activesheet?

Sub DropButton_Click()
Dim i As Long, j As Long
j = range("teamlisttop").row - 1 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

Thanks Dave. I thought I had tried that before and it didn't work. Guess not.
Works perfectly. Is there any way to use a named range TEAMLISTTOP to
designate the starting cell? Thanks again.
Jim T

"Dave Peterson" wrote:

Sub DropButton_Click()
Dim i As Long, j As Long
j = 72 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

--

Dave Peterson


--

Dave Peterson

1 more quick question. Now that I can put the selected names where I need
them, how do I remove those names from the named range TEAMLIST that
originally populated the TeamListBox and then repopulate TEAMLISTBOX with the
new list of names?
--
Jim T


"Dave Peterson" wrote:

Sub DropButton_Click()
Dim i As Long, j As Long
j = 72 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

--

Dave Peterson

I created a userform (is that what you're doing?) and used this code.

Maybe you can modify it to fit your needs:

Option Explicit
Private Sub dropbutton_click()
Dim i As Long
Dim j As Long

j = Worksheets("sheet1").Range("teamlisttop").Row - 1
For i = 0 To teamlistbox.ListCount - 1
If teamlistbox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = teamlistbox.List(i)
End If
Next i

For i = teamlistbox.ListCount - 1 To 0 Step -1
If teamlistbox.Selected(i) Then
Worksheets("sheet1").Range("teamlist").Cells(1) _
.Offset(i, 0).Delete shift:=xlUp
teamlistbox.RemoveItem i
End If
Next i
End Sub

Private Sub UserForm_Initialize()
Dim myCell As Range

With Me.teamlistbox
.MultiSelect = fmMultiSelectMulti
For Each myCell In Worksheets("Sheet1").Range("teamlist")
.AddItem myCell.Value
Next myCell
End With
End Sub


Jim Tibbetts wrote:

1 more quick question. Now that I can put the selected names where I need
them, how do I remove those names from the named range TEAMLIST that
originally populated the TeamListBox and then repopulate TEAMLISTBOX with the
new list of names?
--
Jim T

"Dave Peterson" wrote:

Sub DropButton_Click()
Dim i As Long, j As Long
j = 72 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

--

Dave Peterson


--

Dave Peterson

Wow. The changes to 1st line in the 1st snippet works perfectly to place
names where I need to using the named range as the start point. However, your
2nd snippet:

For i = teamlistbox.ListCount - 1 To 0 Step -1
If teamlistbox.Selected(i) Then
Worksheets("sheet1").Range("teamlist").Cells(1) _
.Offset(i, 0).Delete shift:=xlUp
teamlistbox.RemoveItem i
End If
Next i

really wreaks havoc. It doesn't delete the selected names in TEAMLIST but it
does place names in TEAMLIST from a totaly different areas, changes the
formulas I have in the column to the right of TEAMLIST where these rogue
names are placed and also changes the range value of TEAMLIST (A73:A82 to
A73:A80. After it does this, execution is stopped with a runtime error in the
middle of another procedure called TeamComboBox_Click(). I can't for the life
of me figure out how it is getting to the TeamComboBox_Click() macro. Here is
the 1st part of my macro with the modifications I have made to yours. I
changed the 2nd "For i" to "For h" thinking that might help, but it didn't.
Here is what I have:

Sub DropButton_Click()
Dim h As Long, i As Long, j As Long
<<This part works like charm!
j = Worksheets("TeamData").Range("DROPSTOP").Row - 1
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next
<<This part is causing the problems
For h = TeamListBox.ListCount - 1 To 0 Step -1
If TeamListBox.Selected(h) Then
Worksheets("TeamData").Range("TEAMLIST").Cells(1) _
.Offset(h, 0).Delete shift:=xlUp
TeamListBox.RemoveItem h
End If
Next
<<then more code

Again, the 1st part works great. It's the 2nd part that is the problem. Yes,
this is all on a UserForm. Thanks for working with me on this. I'm having
trouble getting my brain around some of the concepts.
--
Jim T


"Dave Peterson" wrote:

I created a userform (is that what you're doing?) and used this code.

Maybe you can modify it to fit your needs:

Option Explicit
Private Sub dropbutton_click()
Dim i As Long
Dim j As Long

j = Worksheets("sheet1").Range("teamlisttop").Row - 1
For i = 0 To teamlistbox.ListCount - 1
If teamlistbox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = teamlistbox.List(i)
End If
Next i

For i = teamlistbox.ListCount - 1 To 0 Step -1
If teamlistbox.Selected(i) Then
Worksheets("sheet1").Range("teamlist").Cells(1) _
.Offset(i, 0).Delete shift:=xlUp
teamlistbox.RemoveItem i
End If
Next i
End Sub

Private Sub UserForm_Initialize()
Dim myCell As Range

With Me.teamlistbox
.MultiSelect = fmMultiSelectMulti
For Each myCell In Worksheets("Sheet1").Range("teamlist")
.AddItem myCell.Value
Next myCell
End With
End Sub


Jim Tibbetts wrote:

1 more quick question. Now that I can put the selected names where I need
them, how do I remove those names from the named range TEAMLIST that
originally populated the TeamListBox and then repopulate TEAMLISTBOX with the
new list of names?
--
Jim T

"Dave Peterson" wrote:

Sub DropButton_Click()
Dim i As Long, j As Long
j = 72 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

--

Dave Peterson


--

Dave Peterson

The 2nd snippet of code worked fine for me.

But it does delete the cell in TeamList--and that can foul up existing
formulas. Maybe the whole row should be deleted--maybe the x cells to the right
should be deleted???

Worksheets("sheet1").Range("teamlist").Cells(1) _
.Offset(i, 0).resize(1,x).Delete shift:=xlUp

As for the combobox change being fired...

Do you have a linkedcell for the combobox on that same sheet? If yes, try
removing the linkedcell and doing the assignments in code.

If this doesn't help, maybe you can define where those ranges are--what sheet(s)
and what addresses.

Jim Tibbetts wrote:

Wow. The changes to 1st line in the 1st snippet works perfectly to place
names where I need to using the named range as the start point. However, your
2nd snippet:

For i = teamlistbox.ListCount - 1 To 0 Step -1
If teamlistbox.Selected(i) Then
Worksheets("sheet1").Range("teamlist").Cells(1) _
.Offset(i, 0).Delete shift:=xlUp
teamlistbox.RemoveItem i
End If
Next i

really wreaks havoc. It doesn't delete the selected names in TEAMLIST but it
does place names in TEAMLIST from a totaly different areas, changes the
formulas I have in the column to the right of TEAMLIST where these rogue
names are placed and also changes the range value of TEAMLIST (A73:A82 to
A73:A80. After it does this, execution is stopped with a runtime error in the
middle of another procedure called TeamComboBox_Click(). I can't for the life
of me figure out how it is getting to the TeamComboBox_Click() macro. Here is
the 1st part of my macro with the modifications I have made to yours. I
changed the 2nd "For i" to "For h" thinking that might help, but it didn't.
Here is what I have:

Sub DropButton_Click()
Dim h As Long, i As Long, j As Long
<<This part works like charm!
j = Worksheets("TeamData").Range("DROPSTOP").Row - 1
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next
<<This part is causing the problems
For h = TeamListBox.ListCount - 1 To 0 Step -1
If TeamListBox.Selected(h) Then
Worksheets("TeamData").Range("TEAMLIST").Cells(1) _
.Offset(h, 0).Delete shift:=xlUp
TeamListBox.RemoveItem h
End If
Next
<<then more code

Again, the 1st part works great. It's the 2nd part that is the problem. Yes,
this is all on a UserForm. Thanks for working with me on this. I'm having
trouble getting my brain around some of the concepts.
--
Jim T

"Dave Peterson" wrote:

I created a userform (is that what you're doing?) and used this code.

Maybe you can modify it to fit your needs:

Option Explicit
Private Sub dropbutton_click()
Dim i As Long
Dim j As Long

j = Worksheets("sheet1").Range("teamlisttop").Row - 1
For i = 0 To teamlistbox.ListCount - 1
If teamlistbox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = teamlistbox.List(i)
End If
Next i

For i = teamlistbox.ListCount - 1 To 0 Step -1
If teamlistbox.Selected(i) Then
Worksheets("sheet1").Range("teamlist").Cells(1) _
.Offset(i, 0).Delete shift:=xlUp
teamlistbox.RemoveItem i
End If
Next i
End Sub

Private Sub UserForm_Initialize()
Dim myCell As Range

With Me.teamlistbox
.MultiSelect = fmMultiSelectMulti
For Each myCell In Worksheets("Sheet1").Range("teamlist")
.AddItem myCell.Value
Next myCell
End With
End Sub


Jim Tibbetts wrote:

1 more quick question. Now that I can put the selected names where I need
them, how do I remove those names from the named range TEAMLIST that
originally populated the TeamListBox and then repopulate TEAMLISTBOX with the
new list of names?
--
Jim T

"Dave Peterson" wrote:

Sub DropButton_Click()
Dim i As Long, j As Long
j = 72 '<-- added
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Jim Tibbetts wrote:

How do I modify this code to place the selected names from a MultiSelect
listbox starting in cell A73 not A1?

Sub DropButton_Click()
Dim i As Long, j As Long
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
j = j + 1
Cells(j, "A").Value = TeamListBox.List(i)
End If
Next

Thanks
--
Jim T

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson