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

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

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

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

When you say it "deletes the cell" does it actaully delete part of the row?
If so, that isn't going to work. I just need to delete the names in the cells
based on the TeamListBox selections. As far as deleting the entire row - no
can do. Too many columns of data. Any way to just delete names not cells?
--
Jim T


"Dave Peterson" wrote:

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

This line:

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

Deletes a single cell.

Just like when you rightclick on the cell and choose delete, then shift cells
up.

But formulas that point to that cell will be broken.

What do you want to happen?

Jim Tibbetts wrote:

When you say it "deletes the cell" does it actaully delete part of the row?
If so, that isn't going to work. I just need to delete the names in the cells
based on the TeamListBox selections. As far as deleting the entire row - no
can do. Too many columns of data. Any way to just delete names not cells?
--
Jim T

"Dave Peterson" wrote:

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


--

Dave Peterson

"Dave Peterson" wrote in message
...
This line:

Worksheets("sheet1").Range("teamlist").Cells(1) _
.Offset(i, 0).ClearContents '<<<<instead delete

Deletes a single cell.

Just like when you rightclick on the cell and choose delete, then shift
cells
up.

But formulas that point to that cell will be broken.

What do you want to happen?

Jim Tibbetts wrote:

When you say it "deletes the cell" does it actaully delete part of the
row?
If so, that isn't going to work. I just need to delete the names in the
cells
based on the TeamListBox selections. As far as deleting the entire row -
no
can do. Too many columns of data. Any way to just delete names not cells?
--
Jim T

"Dave Peterson" wrote:

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


--

Dave Peterson

OK - That's what I thought. I can't delete the cells, I just need to delete
the names in the cells based on the TeamListBox selections. Here is an
explanation of what I am trying to do. I have a UserForm that contains
TeamComboBox, TeamListBox, AddsListBox, Dropbutton and AddButton. All named
ranges are on sheet TeamData. I am copying a list of names (up to 10 names)
from another location in the same sheet based on formulas that get a team
name from a selection in TeamComboBox. I then populate the named range
TEAMLIST (10 rows long) and using TEAMLIST I populate TeamListBox with those
names (just the names, no blanks). When names in TeamListBox are selected and
the DropButton is clicked, I am putting those selected names in a named range
called DROPS (thanks to your help). I then copy those names in DROPS and
paste them in a different location in the same sheet. Up to this point
everything is working. What I still want to do is delete those selected names
from TEAMLIST so that I can re-populate TeamListBox with the updated list.
Once this is figured out I think I can apply the same concepts for the ADDS.
I'm getting mighty verbose here, aren't I? Am I making any sense? Thanks for
being so patient.
--
Jim T


"Dave Peterson" wrote:

This line:

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

Deletes a single cell.

Just like when you rightclick on the cell and choose delete, then shift cells
up.

But formulas that point to that cell will be broken.

What do you want to happen?

Jim Tibbetts wrote:

When you say it "deletes the cell" does it actaully delete part of the row?
If so, that isn't going to work. I just need to delete the names in the cells
based on the TeamListBox selections. As far as deleting the entire row - no
can do. Too many columns of data. Any way to just delete names not cells?
--
Jim T

"Dave Peterson" wrote:

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


--

Dave Peterson

I'm still confused.

If the user clicks the DropButton, then the selected names get copied to a
different location (Drops).
Then you repopulate the TeamList range and the listbox?

Option Explicit
Private Sub DropButton_Click()

Dim i As Long
Dim j As Long

j = Worksheets("sheet1").Range("drop").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
Call RePopulateRangeAndListbox

End Sub
Private Sub UserForm_Initialize()
Call RePopulateRangeAndListbox
End Sub

Private Sub RePopulateRangeAndListbox()
Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("Sheet1").Range("teamlist")

'do what you need to repopulate the range
'I just put junk test data
With myRng
.Formula = "=""AA""&rand()"
.Value = .Value
End With

With Me.TeamListBox
.Clear
.MultiSelect = fmMultiSelectMulti
For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
.AddItem myCell.Value
End If
Next myCell
End With

End Sub


Jim Tibbetts wrote:

OK - That's what I thought. I can't delete the cells, I just need to delete
the names in the cells based on the TeamListBox selections. Here is an
explanation of what I am trying to do. I have a UserForm that contains
TeamComboBox, TeamListBox, AddsListBox, Dropbutton and AddButton. All named
ranges are on sheet TeamData. I am copying a list of names (up to 10 names)
from another location in the same sheet based on formulas that get a team
name from a selection in TeamComboBox. I then populate the named range
TEAMLIST (10 rows long) and using TEAMLIST I populate TeamListBox with those
names (just the names, no blanks). When names in TeamListBox are selected and
the DropButton is clicked, I am putting those selected names in a named range
called DROPS (thanks to your help). I then copy those names in DROPS and
paste them in a different location in the same sheet. Up to this point
everything is working. What I still want to do is delete those selected names
from TEAMLIST so that I can re-populate TeamListBox with the updated list.
Once this is figured out I think I can apply the same concepts for the ADDS.
I'm getting mighty verbose here, aren't I? Am I making any sense? Thanks for
being so patient.
--
Jim T

"Dave Peterson" wrote:

This line:

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

Deletes a single cell.

Just like when you rightclick on the cell and choose delete, then shift cells
up.

But formulas that point to that cell will be broken.

What do you want to happen?

Jim Tibbetts wrote:

When you say it "deletes the cell" does it actaully delete part of the row?
If so, that isn't going to work. I just need to delete the names in the cells
based on the TeamListBox selections. As far as deleting the entire row - no
can do. Too many columns of data. Any way to just delete names not cells?
--
Jim T

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

Sorry Dave. I knew I was throwing out too much information and it would get
confusing. Forget the Drops part. What I still want to do is delete those
selected names from TEAMLIST so that I can re-populate TeamListBox with the
updated list.
--
Jim T


"Dave Peterson" wrote:

I'm still confused.

If the user clicks the DropButton, then the selected names get copied to a
different location (Drops).
Then you repopulate the TeamList range and the listbox?

Option Explicit
Private Sub DropButton_Click()

Dim i As Long
Dim j As Long

j = Worksheets("sheet1").Range("drop").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
Call RePopulateRangeAndListbox

End Sub
Private Sub UserForm_Initialize()
Call RePopulateRangeAndListbox
End Sub

Private Sub RePopulateRangeAndListbox()
Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("Sheet1").Range("teamlist")

'do what you need to repopulate the range
'I just put junk test data
With myRng
.Formula = "=""AA""&rand()"
.Value = .Value
End With

With Me.TeamListBox
.Clear
.MultiSelect = fmMultiSelectMulti
For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
.AddItem myCell.Value
End If
Next myCell
End With

End Sub


Jim Tibbetts wrote:

OK - That's what I thought. I can't delete the cells, I just need to delete
the names in the cells based on the TeamListBox selections. Here is an
explanation of what I am trying to do. I have a UserForm that contains
TeamComboBox, TeamListBox, AddsListBox, Dropbutton and AddButton. All named
ranges are on sheet TeamData. I am copying a list of names (up to 10 names)
from another location in the same sheet based on formulas that get a team
name from a selection in TeamComboBox. I then populate the named range
TEAMLIST (10 rows long) and using TEAMLIST I populate TeamListBox with those
names (just the names, no blanks). When names in TeamListBox are selected and
the DropButton is clicked, I am putting those selected names in a named range
called DROPS (thanks to your help). I then copy those names in DROPS and
paste them in a different location in the same sheet. Up to this point
everything is working. What I still want to do is delete those selected names
from TEAMLIST so that I can re-populate TeamListBox with the updated list.
Once this is figured out I think I can apply the same concepts for the ADDS.
I'm getting mighty verbose here, aren't I? Am I making any sense? Thanks for
being so patient.
--
Jim T

"Dave Peterson" wrote:

This line:

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

Deletes a single cell.

Just like when you rightclick on the cell and choose delete, then shift cells
up.

But formulas that point to that cell will be broken.

What do you want to happen?

Jim Tibbetts wrote:

When you say it "deletes the cell" does it actaully delete part of the row?
If so, that isn't going to work. I just need to delete the names in the cells
based on the TeamListBox selections. As far as deleting the entire row - no
can do. Too many columns of data. Any way to just delete names not cells?
--
Jim T

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

You don't have to update that range. You can just use the .removeitem to update
the listbox.



Jim Tibbetts wrote:

Sorry Dave. I knew I was throwing out too much information and it would get
confusing. Forget the Drops part. What I still want to do is delete those
selected names from TEAMLIST so that I can re-populate TeamListBox with the
updated list.
--
Jim T

"Dave Peterson" wrote:

I'm still confused.

If the user clicks the DropButton, then the selected names get copied to a
different location (Drops).
Then you repopulate the TeamList range and the listbox?

Option Explicit
Private Sub DropButton_Click()

Dim i As Long
Dim j As Long

j = Worksheets("sheet1").Range("drop").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
Call RePopulateRangeAndListbox

End Sub
Private Sub UserForm_Initialize()
Call RePopulateRangeAndListbox
End Sub

Private Sub RePopulateRangeAndListbox()
Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("Sheet1").Range("teamlist")

'do what you need to repopulate the range
'I just put junk test data
With myRng
.Formula = "=""AA""&rand()"
.Value = .Value
End With

With Me.TeamListBox
.Clear
.MultiSelect = fmMultiSelectMulti
For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
.AddItem myCell.Value
End If
Next myCell
End With

End Sub


Jim Tibbetts wrote:

OK - That's what I thought. I can't delete the cells, I just need to delete
the names in the cells based on the TeamListBox selections. Here is an
explanation of what I am trying to do. I have a UserForm that contains
TeamComboBox, TeamListBox, AddsListBox, Dropbutton and AddButton. All named
ranges are on sheet TeamData. I am copying a list of names (up to 10 names)
from another location in the same sheet based on formulas that get a team
name from a selection in TeamComboBox. I then populate the named range
TEAMLIST (10 rows long) and using TEAMLIST I populate TeamListBox with those
names (just the names, no blanks). When names in TeamListBox are selected and
the DropButton is clicked, I am putting those selected names in a named range
called DROPS (thanks to your help). I then copy those names in DROPS and
paste them in a different location in the same sheet. Up to this point
everything is working. What I still want to do is delete those selected names
from TEAMLIST so that I can re-populate TeamListBox with the updated list.
Once this is figured out I think I can apply the same concepts for the ADDS.
I'm getting mighty verbose here, aren't I? Am I making any sense? Thanks for
being so patient.
--
Jim T

"Dave Peterson" wrote:

This line:

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

Deletes a single cell.

Just like when you rightclick on the cell and choose delete, then shift cells
up.

But formulas that point to that cell will be broken.

What do you want to happen?

Jim Tibbetts wrote:

When you say it "deletes the cell" does it actaully delete part of the row?
If so, that isn't going to work. I just need to delete the names in the cells
based on the TeamListBox selections. As far as deleting the entire row - no
can do. Too many columns of data. Any way to just delete names not cells?
--
Jim T

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Okey Dokey. I'll see if I can do that. Thanks for all you've put up with.
--
Jim T


"Dave Peterson" wrote:

You don't have to update that range. You can just use the .removeitem to update
the listbox.



Jim Tibbetts wrote:

Sorry Dave. I knew I was throwing out too much information and it would get
confusing. Forget the Drops part. What I still want to do is delete those
selected names from TEAMLIST so that I can re-populate TeamListBox with the
updated list.
--
Jim T

"Dave Peterson" wrote:

I'm still confused.

If the user clicks the DropButton, then the selected names get copied to a
different location (Drops).
Then you repopulate the TeamList range and the listbox?

Option Explicit
Private Sub DropButton_Click()

Dim i As Long
Dim j As Long

j = Worksheets("sheet1").Range("drop").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
Call RePopulateRangeAndListbox

End Sub
Private Sub UserForm_Initialize()
Call RePopulateRangeAndListbox
End Sub

Private Sub RePopulateRangeAndListbox()
Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("Sheet1").Range("teamlist")

'do what you need to repopulate the range
'I just put junk test data
With myRng
.Formula = "=""AA""&rand()"
.Value = .Value
End With

With Me.TeamListBox
.Clear
.MultiSelect = fmMultiSelectMulti
For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
.AddItem myCell.Value
End If
Next myCell
End With

End Sub


Jim Tibbetts wrote:

OK - That's what I thought. I can't delete the cells, I just need to delete
the names in the cells based on the TeamListBox selections. Here is an
explanation of what I am trying to do. I have a UserForm that contains
TeamComboBox, TeamListBox, AddsListBox, Dropbutton and AddButton. All named
ranges are on sheet TeamData. I am copying a list of names (up to 10 names)
from another location in the same sheet based on formulas that get a team
name from a selection in TeamComboBox. I then populate the named range
TEAMLIST (10 rows long) and using TEAMLIST I populate TeamListBox with those
names (just the names, no blanks). When names in TeamListBox are selected and
the DropButton is clicked, I am putting those selected names in a named range
called DROPS (thanks to your help). I then copy those names in DROPS and
paste them in a different location in the same sheet. Up to this point
everything is working. What I still want to do is delete those selected names
from TEAMLIST so that I can re-populate TeamListBox with the updated list.
Once this is figured out I think I can apply the same concepts for the ADDS.
I'm getting mighty verbose here, aren't I? Am I making any sense? Thanks for
being so patient.
--
Jim T

"Dave Peterson" wrote:

This line:

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

Deletes a single cell.

Just like when you rightclick on the cell and choose delete, then shift cells
up.

But formulas that point to that cell will be broken.

What do you want to happen?

Jim Tibbetts wrote:

When you say it "deletes the cell" does it actaully delete part of the row?
If so, that isn't going to work. I just need to delete the names in the cells
based on the TeamListBox selections. As far as deleting the entire row - no
can do. Too many columns of data. Any way to just delete names not cells?
--
Jim T

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson