Trying to program a button to remove an item from a listbox that references cells

[Caroline_678]

Hi! Hopefully someone can help me out here.

I have a userform that someone can type in a name to add to a list and click a command button and it adds it to another sheet in the document. On the same userform there is a listbox that is filled by using the rowsource method that refers to the specific sheet cells A2:A20. This all works great, you type the name in, click the command button that says "add" and it populates into the listbox and onto the sheet. However I am wanting to program another command button that will essentially remove an item from the list if it selected in the listbox. But it's not as easy as just removing the item because the list is not populated in the code, it's populated by the sheet that it references.

Maybe this doesn't make sense? I'm trying to explain it in a way that makes sense but this is more difficult than I thought! If you have any questions I will try to answer them as best I can. I'm pretty new to VBA, I can make very basic userforms and this is the first time I'm trying to something more than just adding something to a list.

Thanks in advance for any of the help I get!

[mikerickson]

If you are using RowSource to populate a ListBox, then you can't remove individual items from that ListBox. Use a different method.

[Caroline_678]

If you are using RowSource to populate a ListBox, then you can't remove individual items from that ListBox. Use a different method.

what method do you think would be best? I do need what's in the listbox to show up in a different sheet so that I can use that as a reference to some formulas I have spread throughout the document.

[JKwan]

Private Sub UserForm_Initialize()
Me.ListBox1.List = Range("A2:A20").Value
End Sub

Private Sub CommandButton1_Click()
Me.ListBox1.RemoveItem Me.ListBox1.ListIndex
End Sub

[Caroline_678]

Thanks JKwan, the first part of that code works great and the second part would work great except that I need it to remove that information from the cell that the list is referencing, not just remove it from the listbox. Any suggestions there?

So now my problem is: I need to program a command button that says Remove on it to delete the cell in the range that says what you have selected in the listbox. I might be grasping for straws here, let me know! I'm open to completely out of the box suggestions too.

[mdmackillop]

Private Sub UserForm_Initialize()
Me.ListBox1.List = Range("A2:A20").Value
End Sub

Private Sub CommandButton1_Click()
Dim c As Range
Set c = Range("A2:A20").Find(Me.ListBox1, lookat:=xlWhole)
Me.ListBox1.RemoveItem Me.ListBox1.ListIndex
c.Delete shift:=xlUp
End Sub

[Caroline_678]

Thanks mdmackillop! I actually fiddled around with this enough that I came up with a solution. This is the code I use now:


Private Sub Remove_Click() 'REMOVE SELECTION
Dim indexi As Long
indexi = ListBox1.ListIndex + 2
If indexi <> -1 Then
Sheet2.Rows(indexi).EntireRow.Delete
End If


Dim rngSource As Range

'Set reference to the range of data to be filled
Set rngSource = Worksheets("SETTINGS").Range("A2:A20")

'Fill the listbox
'Insert the range of data supplied
ListBox1.List = rngSource.Cells.Value


End Sub