Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How re autonumbering again after deletion row by userform

0

Hi

after searching for last balance for name by combobox1 I will delete by command button1 ,but in this case will ignore arranging numbers in column B . so what I want after deleteing row should arrange numbers by autonumbering again 1,2,3...

Private Sub CommandButton1_Click()

 Dim f As Range
  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
    End If
  End With
End Sub

thanks

Answer
Discuss

Discussion

Hi again Abdo

Your code seems to delete the last entry for the selected user (e.g. Abdo). Do you really want to do that (and to remove transactions, like a Receipt)? That seems okay to correct errors but it doesn't seem like a good idea to me otherwise, to be honest, especially when you want to renumber so it looks liike the original transaction never took place.

Please confirm the purpose of your code in your Question (using careful wording please). and I'll answer later today hopefully.
John_Ru (rep: 6612) Dec 2, '24 at 7:14 am
Also, you're deleting the entire row currently so there's a danger that you'll delete the names in column I (used to populate the list for ComboBox1).
John_Ru (rep: 6612) Dec 2, '24 at 8:11 am
Your code seems to delete the last entry for the selected user (e.g. Abdo). Do you really want to do that (and to remove transactions, like a Receipt)?
yes because   usually when delete row for the name will be the last entered for the name.
Also, you're deleting the entire row currently so there's a danger that you'll delete the names in column I (used to populate the list for ComboBox1). that's correct , thanks for this spotting.
well in this case I will search for way by code to populate automatically instead of use column inside sheet.
Abdo M (rep: 20) Dec 2, '24 at 12:40 pm
Thanks Abdo. I will try to answer later tonight or tomorrow (including how to populate ComboBox1). 
John_Ru (rep: 6612) Dec 2, '24 at 1:23 pm
Add to Discussion

Answers

1
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.

Discuss

Discussion

Perfect!
many thanks for assisatance.
Abdo M (rep: 20) Dec 3, '24 at 3:14 am
Thanks for selecting my Answer. Abdo. (I just corrected a minor typo in the text of my Answer.) 
John_Ru (rep: 6612) Dec 3, '24 at 5:55 am
@John,

Nicely done.

Cheers   :-)
WillieD24 (rep: 657) Dec 3, '24 at 10:42 am
@Willie
Thanks!
John_Ru (rep: 6612) Dec 3, '24 at 2:08 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login