VBA buttons: add / delete a row where clicked

DDelainy

New Member
Joined
Dec 22, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    10.7 KB · Views: 100

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
you can try something like:
VBA Code:
Sub DeleteRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Delete
     End If
   End With
End Sub


for insert:
VBA Code:
Sub DeleteRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Offset(1, 0).Insert
     End If
   End With
End Sub
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
As for the insert script...I failed to change the name of the macro...you can try this
VBA Code:
Sub InsertRow_Click()
   With Selection
     If .Areas.Count = 1 Then
        .EntireRow.Offset(1, 0).Insert
     End If
   End With
End Sub
 
Upvote 0
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 :
VBA Code:
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....

VBA Code:
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 :
1672342575965.png
 
Last edited:
Upvote 0
Let's move your Shapes according to what you want (insert or delete)...
Code to insert in you Sheet :
VBA Code:
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....

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,180
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top