Search code examples
excelbuttonexcel-2010delete-rowvba

Adding row at the end of existing ones and create a delete button VBA Excel 2010


Sorry for my poor English. I'm trying.

I want a script that allows me to add a row at the end of already existing ones and that also adds a button at the very beginning of this row so users can deleted it if needed.

I am copying the first row of the Worksheet because I have some formatting and equations i wished to keep.

This is the script that adds the new row :

Private Sub addRow_Click()

Application.ScreenUpdating = False
ActiveSheet.Unprotect

Rows("1:1").Hidden = False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Range("LastRow").Select

Selection.Insert Shift:=xlDown
Rows("1:1").Hidden = True
Application.CutCopyMode = False

ActiveSheet.Protect
Application.ScreenUpdating = True

End Sub

Can you help me?


Solution

  • Found an answer. Not sure if it is the best way to do it, but it is working in my case!

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
            ActiveSheet.Unprotect
        Application.EnableEvents = False
        On Error GoTo ErrorHandler
    
        If UCase(Target.Value) = "X" Then
            ActiveCell.EntireRow.Delete Shift = xlUp
        End If
    
        ExitHere:
            Cancel = True
            Application.EnableEvents = True
            ActiveSheet.Protect
            Exit Sub
        ErrorHandler:
            Resume ExitHere
    
    
        End Sub