I am having some problems with the following code on a userform used to delete records on the sheet:
Private Sub CommandButton4_Click()
Set Rng = Range("A3:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If Username.Value = Rng Then
Msg = "Are you sure you want to delete the username: " & Username.Value & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNo)
Select Case Ans
Case vbYes
With Username
If .ListIndex > -1 Then 'Test if something is selected
Worksheets("Users").Range("A" & 1 + .ListIndex).EntireRow.Delete Shift:=xlUp
.RemoveItem .ListIndex
End If
.ListIndex = -1 'deselect
End With
Exit Sub
Case vbNo
Exit Sub
End Select
Else
MsgBox "Username does not exits. Please selet a user to delete from the Username dropdown list."
End If
End Sub
The code lines with the apostrophe is giving me error 13 type mismatch. Im using it to first check that the combo box username.value exists in the range (A Column row 3 on to the last row).
The rest of the code then correctly removes the item from the username combo box list, but is removing the wrong entry on the sheet. I think its because the range starts at A3, because if I say delete the 5th item in the combo box list, which would be in A7, it removes the fifth row on the sheet instead, A5.
Bookmarks