excel vba - automatically add/delete buttons depending on cell values

Question

What I'm trying to do...

Get a button to automatically appear in cell H1 if there is something in cell A1. This is continued for multiple buttons down the column if more content is in the cells below A1. Each button when used will cut the contents of the cells from column A to G in the same row as the used button and paste them in the first blank row of another sheet and remove the used button.

First problem...

Adding a button in H1 if A1 is not blank. Remove/delete button in H1 if A1 is blank.

Edit 1:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Buttons.Add(423.75, 0, 48, 15).Select
    'ActiveSheet.Shapes("Button1").Name = "Button1"
    Selection.Name = "Button1"
    Selection.Characters.Text = "REMOVE"
    With Selection.Characters(Start:=1, Length:=6).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
End Sub

The problem is using an if statement to put multiple buttons, each with the name "Button" followed by the number of what row its in (Button1, Button2, etc).

Edit 2:

Title change.

Old - excel vba - add/remove buttons and cell range

New - excel vba - automatically add/delete buttons depending on cell values

You need to demonstrate that you attempted to solve this problem yourself. This site is for specific questions, it's unlikely that somebody will build something from scratch for you based on a vague scoping desire. — n8., Oct 26 '17 at 16:22
Also, since new people tend not to upvote or flag answers, spending our time answering questions such as these tends not to pay off. — n8., Oct 26 '17 at 16:23
I will post something I tried but it doesn't delete or add a button if I change what is in cell A1. Just hides it if blank or doesn't hide the button otherwise. @n8. — Daniel Vaughn, Oct 26 '17 at 16:26
I'm someone that doesn't tend to vote because of the lack of experience and would rather not change the reputation of an answer or question due to my opinion about it. — Daniel Vaughn, Oct 26 '17 at 16:29
I don't have a way to show an attempt to solve this myself because I can't get anything to work. I don't have much experience with vba. I just need what I have explained above the comments. — Daniel Vaughn, Oct 26 '17 at 16:39
https://stackoverflow.com/questions/8250050/adding-command-buttons-to-worksheet-at-run-time-and-also-define-events, https://stackoverflow.com/questions/31163591/programmatically-add-several-buttons-to-an-excel-worksheet-with-vba, https://stackoverflow.com/questions/8949641/vba-add-button-set-the-caption-and-link-it-to-a-method — Tim Williams, Oct 26 '17 at 16:49
What you describe is a set of problems. This merits a set of individual questions, not a single blanket question. Most of us can appreciate the position you're in as we all started coding somewhere. Please just focus your questions on specific problems. You may find that 10 posts in you are building your reputation, becoming proficient, and becoming engaged with the community. This is all good, so please take the time to do it this way. — n8., Oct 26 '17 at 17:08
I've listed the current problem as "First problem...". This is all I'm concerned with at the moment. I can't break it down more than this. I know there is multiple problems altogether in this but I'm trying to state what I want to do then the problems on the way.@n8. — Daniel Vaughn, Oct 26 '17 at 17:13
I've tried recording a macro but its missing several things I need. What's missing is the if statements for the row the button is in to tell if the button should be add/removed. how would i write that into the macro? I have If cells(row(), 1) = "" Then ... this doesn't work — Daniel Vaughn, Oct 26 '17 at 17:46
OK - then break that down: do you know how to check if a cell is blank? "how would i write that into the macro" - look up the syntax for `If Then` in VBA, and try to use that to add something to your recorded macro: if you run into problems then post the code along with a description of what the problem is. — Tim Williams, Oct 26 '17 at 17:46
I can check if a certain cell is blank if it is supposed to be a certain cell. If it's a cell in a specific row then no. The blank cell can be in any row but only in column A — Daniel Vaughn, Oct 26 '17 at 17:49
I would just like to say this is why I hate using forums. Showing the problem would be easier than trying to type out the problem(s). — Daniel Vaughn, Oct 26 '17 at 17:51
How am I supposed to show my macro code in a comment? This forum thing is complicated to do that. Everything just shows as a paragraph. — Daniel Vaughn, Oct 26 '17 at 17:54
added the code snippet to the post above under edit 1. @TimWilliams — Daniel Vaughn, Oct 26 '17 at 18:24

score 0 · Answer 1 · answered Oct 26 '17 at 23:15

0

This will add a button on any row where there's content in A1:A10, and remove any existing button (that was added by this code) if there's no content

Sub Macro1()

    Dim c As Range, sht As Worksheet, btn, btnName As String

    Set sht = ActiveSheet

    For Each c In sht.Range("A1:A10").Cells '<< cells to check for content

        btnName = "btnRow_" & c.Row 'name the button according to the row

        If Len(c.Value) > 0 Then
            With c.EntireRow.Cells(1, "H")
                Set btn = sht.Buttons.Add(.Left, .Top, .Width, .Height)
            End With
            btn.Name = btnName
            btn.Characters.Text = "REMOVE"
        Else
            'delete the button if it exists (ignore any error if not found)
            On Error Resume Next
            sht.Shapes(btnName).Delete
            On Error GoTo 0
        End If

    Next c

End Sub

answered Oct 26 '17 at 23:15

Tim Williams

154,628
8
97
125

I added the code to the sheet and it's not working. @TimWilliams – Daniel Vaughn Oct 27 '17 at 04:01
It doesn't do anything. @TimWilliams – Daniel Vaughn Oct 27 '17 at 04:08
So you run it and nothing happens, even if there's content in A1:A10? – Tim Williams Oct 27 '17 at 04:10
it only works if i add a button and assign that macro to it. I need it to work with all of column A and automatically add or remove the buttons – Daniel Vaughn Oct 27 '17 at 04:17
This code will automatically hide or unhide a command button but I can't seem to modify it to work for what i want. I don't get it.`Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Cells(1, 1).Value <> "1" Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.ScreenUpdating = True End Sub` @TimWilliams – Daniel Vaughn Oct 27 '17 at 04:38
I caught the difference. `Private Worksheet_Change(...)`. I put that in place of `Macro1()` in your code and it worked automatacilly. It still needs modification because it places multiple buttons on top of each other if a cell already has a value when putting in a value in a different cell. @TimWilliams – Daniel Vaughn Oct 28 '17 at 15:29

Daniel Vaughn · Accepted Answer · 2017-11-01T20:31:00.083

This is the end result of what I was looking for. Thanks for all the help.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim This As Worksheet, _
        RemoveButton, _
        ButtonName As String

    Set This = Sheets("SheetName1")
    ButtonName = "REMOVE" & Target.Row

    If Target.Column = 1 Then
        If This.Range("$A$" & Target.Row) <> "" Then
            On Error Resume Next
            This.Shapes(ButtonName).Delete
            On Error GoTo 0

            With Target.EntireRow.Cells(1, "H")
                Set RemoveButton = This.Buttons.Add(.Left, _
                                                    .Top, _
                                                    .Width, _
                                                    .Height)
            End With

            RemoveButton.Name = ButtonName
            RemoveButton.Characters.Text = "REMOVE"
            RemoveButton.OnAction = "REMOVE_BUTTON_ACTION"
        Else
            On Error Resume Next
            This.Shapes(ButtonName).Delete
            On Error GoTo 0
        End If
    End If
End Sub

There are some bugs but they don't seem to be a big deal. For instance, if I paste multiple rows in column A then it only creates 1 button in the first row of the paste range.

Sub REMOVE_BUTTON_ACTION()
    Dim RemoveButton As Object, _
        ButtonColumn As Integer, _
        ButtonRow As Integer, _
        RemovedSheetRow As Integer

    Set RemoveButton = ActiveSheet.Buttons(Application.Caller)
    With RemoveButton.TopLeftCell
        ButtonRow = .Row
    End With
    RemovedSheetRow = Worksheets("SheetName2").Range("$J$1").Value + 1

    Range("A" & ButtonRow & ":G" & ButtonRow).Cut _
        Destination:=Sheets("SheetName2").Range("A" & RemovedSheetRow)
End Sub

I have a value stored in J1 for the number of cells containing something in column A. J1 actually contains a COUNTIFS() formula.

Again, thanks for all the help.

excel vba - automatically add/delete buttons depending on cell values

2 Answers2

Linked