How do I delete a specific item in a combobox list, when the user writes the name of the entry to be deleted in a dialogbox

Question

So my problem is mostly explained in the title,I have a button that when pressed will ask "what do you want to delete?". Then the user writes what to delete and it is deleted from the list.

Sub DeleteShip()

Dim a As Variant
Dim b As String

a = InputBox("Vælg fartøj der skal slettes")
b = CStr(a)

If Sheet1.ShipList.Value = b Then
Sheet1.ShipList.RemoveItem (ShipList.ListIndex(b))
Else
MsgBox "Det skib findes ikke i listen, har du stavet det korrekt?"
End If
End Sub

It all depends.... what kind of combobox and what's the source? You've got a control on a form, or a form control on the sheet, or an activex control, or data validation combobox. Then the list can be typed directly in as a source, or linked to a range on the spreadsheet... maybe some other way. And as @Olly said.... — Darren Bartrup-Cook, Jun 28 '18 at 10:12
Its an activex combobox, on a sheet. the box is linked to a cell. I have tried list.removeitem (list.listindex) dones't work it says i need an object which is what i'm giving it. — Linexxlol, Jun 28 '18 at 10:20
Look here for inspiration: https://stackoverflow.com/questions/4200712/dynamically-set-listfillrange-in-excel-combobox-using-vba/48804129. The listfillrange refering to a dynamic named range should be perfect for you. Just delete the row with the typed value in a sub. — JvdV, Jun 28 '18 at 11:04
@JvdV That seems like overdoing it, can't i just somehow compare what the user writes in a dialogbox and compare it with the contents of my combobox, and if it exsists, remove it. — Linexxlol, Jun 28 '18 at 11:29

Darren Bartrup-Cook · Answer 1 · 2018-06-28T13:32:06.493

Try the code below. You may have to update the name of ComboBox1.

To find the name:
- Select the combobox in Design Mode. - Click the Format ribbon.
- In the Arrange section click the Selection Pane.
- All shapes, their names & visibility will appear in a separate pane.

This will work if your using a range to fill your combobox:

Sub DeleteShp()

    Dim a As String
    Dim rFound As Range
    Dim LookupRange As Range

    Set LookupRange = Range(Sheet1.ComboBox1.ListFillRange)

    a = Trim(InputBox("Vælg fartøj der skal slettes"))

    With LookupRange
        Set rFound = .Find(What:=a, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
        If Not rFound Is Nothing Then
            rFound.Delete Shift:=xlUp
        End If
    End With

End Sub

If you've populated your combobox using AddItem:

Sub InitialiseShp()
'Fills combobox with "Item A" to "Item F".
    Dim x As Long

    Sheet1.ComboBox1.Clear

    For x = 65 To 70
        Sheet1.ComboBox1.AddItem "Item " & Chr(x)
    Next x

End Sub

You'll need to search the items to return the correct index number to remove:

Sub DeleteShp_2()

    Dim a As String
    Dim x As Long

    a = Trim(InputBox("Vælg fartøj der skal slettes"))

    With Sheet1.ComboBox1
        For x = 0 To .ListCount - 1
            If .List(x) = a Then
                .RemoveItem x
                Exit For
            End If
        Next x
    End With

End Sub

Thanks but i get an error "Method "range" of object "_Global" failed". — Linexxlol, Jun 28 '18 at 13:02
Let me guess.... on the `Set LookupRange = Range(Sheet1.ComboBox1.ListFillRange)` line? How is the combo-box populated? What do you have in the `ListFillRange` property? — Darren Bartrup-Cook, Jun 28 '18 at 13:05
Okay i may not have explained properly. The ListFillRange is empty, i don't have an actual list that i'm linking to. Should i use an array instead? — Linexxlol, Jun 28 '18 at 13:12

How do I delete a specific item in a combobox list, when the user writes the name of the entry to be deleted in a dialogbox

1 Answers1