VBA buttons: add / delete a row where clicked

[DDelainy]

Hi everyone,
For the love of god I couldn't figure it out.

I have a document (all on one sheet) that has multiple sections. In each section there could be multiple rows "problems". I'm trying to add two buttons at the end of each row ("problem") to either ADD another row below where it's clicked (to add another "problem" directly below it) or DELETE the row where it's clicked (when "problem" is resolved). The top row ("problem") in each section will have only ADD button as there is nothing to delete. And each added row will have both buttons. I'll use "+" (add) or "x" (delete) as button names.

I tried copying an empty row with buttons from hidden row but I only can paste it under/above a certain row, so basically if I add problem, the whole list will be shifting down or up, I couldn't figure out how to add row underneath where "+" is clicked. I also tried double-click but it only work in relation to "active" cell, which won't be necessarily the case every time a row is added or deleted.

Finally, instead of ActiveX button (as their margins are too big) I decided to create a shape and assign macro to it.

Thanks so much for any suggestions! I think at this point my brain is totally fried trying to figure this out.

 

[Candyman8019]

you can try something like:

Sub DeleteRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Delete
     End If
   End With
End Sub

for insert:

Sub DeleteRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Offset(1, 0).Insert
     End If
   End With
End Sub

 

[DDelainy]

Thanks Candyman8019
This unfortunately won't work as it deletes the row where a cell is selected, not where "x" button is clicked.
I couldn't make insert code work either.

 

[Candyman8019]

There may be a way, although, I'm not aware of one...to link a picture to a specific cell in order to identify the row it is applicable to. So short of creating a macro for each button for each row, I'm at a loss.

I would suggest having an add and a delete button at the top and instruct the users to select the applicable row before clicking one of the buttons. This would also allow for a cleaner interface.

 

[Candyman8019]

As for the insert script...I failed to change the name of the macro...you can try this

Sub InsertRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Offset(1, 0).Insert
     End If
   End With
End Sub

 

[DDelainy]

Thanks @Candyman8019

Does anyone have any other suggestions? Thanks!

 

[Flaiban]

Thanks @Candyman8019

Does anyone have any other suggestions? Thanks!

Let's move your Shapes according to what you want (insert or delete)...
Code to insert in you Sheet :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A2:F500")) Is Nothing Then
    Range("AZ2").Value = Target.Row
    Call Insert_Delete
    Else

End If

End Sub

After....

Option Explicit
Dim SelectRow As Long

Sub Insert_Delete()

With ActiveSheet
    With .Shapes("Delete") 'Rename your shape for Delete , or change "Delete" for your choice
        .Visible = msoCTrue
        .Left = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6).Left
        .Top = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6).Top
        .IncrementLeft 31
    End With
End With

With ActiveSheet
    With .Shapes("Insert") ''Rename your shape for Insert , or change "Insert" for your choice
        .Visible = msoCTrue
        .Left = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6).Left
        .Top = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6).Top
        .IncrementLeft 2
    End With
End With

End Sub

Sub DeleteRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Delete
     End If
   End With
End Sub

Sub InsertRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Insert
     End If
   End With
End Sub

Example :

 

[DDelainy]

Let's move your Shapes according to what you want (insert or delete)...
Code to insert in you Sheet :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A2:F500")) Is Nothing Then
    Range("AZ2").Value = Target.Row
    Call Insert_Delete
    Else

End If

End Sub

After....

Option Explicit
Dim SelectRow As Long

Sub Insert_Delete()

With ActiveSheet
    With .Shapes("Delete") 'Rename your shape for Delete , or change "Delete" for your choice
        .Visible = msoCTrue
        .Left = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6).Left
        .Top = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6).Top
        .IncrementLeft 31
    End With
End With

With ActiveSheet
    With .Shapes("Insert") ''Rename your shape for Insert , or change "Insert" for your choice
        .Visible = msoCTrue
        .Left = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6).Left
        .Top = ActiveSheet.Range("A" & ActiveCell.Row).Offset(0, 6).Top
        .IncrementLeft 2
    End With
End With

End Sub

Sub DeleteRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Delete
     End If
   End With
End Sub

Sub InsertRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Insert
     End If
   End With
End Sub

Example :
View attachment 81750

Thanks @Flaiban, I'll work with this option.

What if a cell would contain a text (not a shape) and a row would be inserted below or current row deleted by double-clicking for either option? Like if a cell has text "+" and double-clicking results in inserting a row below and if a cell has text "-" double-clicking results in deleting that row. This will have to be for specific cells only.