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