Selected Answer
Abdo
In the attached revised file, the list of ComboBox1 does not use the named worksheet range "LIST" but is set by this code:
Private Sub UserForm_Initialize()
'populate the CB list from a fixed array (rather than a worksheet range)
ComboBox1.List = Array("Abdo", "addo", "abed")
End Sub
This means that deleted rows cannot affect the CB list (which was previously cells in column I).
Your code to delete the entire row can now work but is modifed by the changes in bold below:
Private Sub CommandButton1_Click()
Dim f As Range, n As Long
With Worksheets("VCC")
Set f = .Columns(4).Find(ComboBox1.Value, , xlValues, xlWhole, SearchDirection:=xlPrevious)
If Not f Is Nothing Then
f.Offset(, 3).EntireRow.Delete
' renumber the records in columns B
For n = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
.Cells(n, 2).Value = n - 1
Next n
' then close UF
Unload Me
End If
End With
End Sub
I closed the UserForm after deletion since I have also used the ComboBox1_Change code to alter the text on CommandButton1 of the UserForm (in bold again):
Private Sub ComboBox1_Change()
Dim f As Range
With Worksheets("VCC")
Set f = .Columns(4).Find(ComboBox1.Value, , xlValues, xlWhole, SearchDirection:=xlPrevious)
If Not f Is Nothing Then
TextBox1.Value = f.Offset(, 3).Text
'change the command button text and colour
Me.CommandButton1.Caption = "Delete last Receipt (row " & f.Row & ")"
Else
Me.CommandButton1.Caption = "No record to delete"
End If
End With
' rewrite the button if CB1 entry is cleared
If ComboBox1.Value = "" Then Me.CommandButton1.Caption = "No record to delete"
End Sub
:
Now, if you change the Name, the button will say which row it will delete.
If there's no match (e.g. you deleted a name or wrote one not set by the UserForm_Initialize sub) then it says "No record to delete".
Hope this helps. If so, please mark this Answer as Selected.