Recently I had to make a file where the challenge was to create an easy way for the user to remove any rows from the dataset.

I know… Right click->Delete row.

Trust me. It had to be a lot simpler than that. laughing

 

So I created a macro that places a button in the row when something gets entered in Column A.
The user than can click on this newly made button to do something (in my case this was to delete the button itself and the row below it.)

Adding button to spreadsheet with VBA

WHAT WE WANT TO ACHIEVE IS THIS:

 

We want to add a button to the same row where the user writes something in to the watched cells.

The button is pre-loaded with a call function that will trigger a macro when pressed.

Note: that in this case the triggered macro will give us a messagebox for testing purposes.

In my production file this macro deleted the button and the corresponding row.

Let’s look at the Code!

 

The code uses two part in my case.

The Worksheet_Change event in the worksheet module

The macro we trigger in a normal module

Paste it to the worksheet

Option Explicit

Public Thetarget As Range
Private Sub Worksheet_Change(ByVal Target As Range)
    ' On Error Resume Next
    Dim Delbutton As Object

    If Not Intersect(Target, Range("A5:A10")) Is Nothing Then
        If Target.Value <> 0 Then
            Set Thetarget = Cells(Target.Row, 5)
            If Thetarget.Value <> 1 Then
                Thetarget.Value = 1
                Set Delbutton = Sheet1.Buttons.Add(Top:=Thetarget.Top, _
                    Left:=Thetarget.Left, Height:=Thetarget.Height, _
                    Width:=Thetarget.Width)
                With Delbutton
                    .Caption = "Test"
                    .OnAction = "DeleteMe"
                End With
            Else
            
                Exit Sub
            
            End If
        End If
    End If
End Sub

Paste this in to the module

Option Explicit
Public Sub DeleteMe()
    MsgBox "Do something."
End Sub

HOW THE CODE WORKS?

 

If you have time I like to explain how the code works rather than just you grabbing it an pasting it nilly-willy.
So here it goes:

Option Explicit – This will force us to use declared variables (You can delete if you want to.)

Public Thetarget As Range – Declare variable as a public variable

Private Sub Worksheet_Change(ByVal Target As Range) – The Worksheet changeevent

‘ On Error Resume Next – On error continue (commented out)
Dim Delbutton As Object – Declare a Button as an object

If Not Intersect(Target, Range(“A5:A10“)) Is Nothing Then – Change this range to modify of what you want to watch for the change event

If Target.Value <> 0 Then – If the target cell’s value is not 0 then…
Set Thetarget = Cells(Target.Row, 5) – Set the “Thetarget” variable to target cell’s row and the fifth column

If Thetarget.Value <> 1 Then – Check if there is a button already (we putting a 1 under the button to achieve this)

Thetarget.Value = 1 – If no button then put a 1 in the cell underneath the button
Set Delbutton = Sheet1.Buttons.Add(Top:=Thetarget.Top, _
Left:=Thetarget.Left, Height:=Thetarget.Height, _
Width:=Thetarget.Width) – Setting the button’s position and size to the same as the cell underneath

With Delbutton

.Caption = “Test– This will be written on your button
.OnAction = “DeleteMe– This will be the name of the PUBLIC macro that your button will call on a press

End With

Else

Exit Sub

End If

End If

End If

End Sub

Sorry about the messy formatting my text module is horrible to format text as a code.

Where do I paste the code I found on the internet?

We have a full post about just that!
Check it out HERE

 

Check out or Facebook page!

We have a facebook page where you can drop us a like or follow us. This way you can get notified of any new content that comes out.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]