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.
– n8., Commented Oct 26, 2017 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.
– n8., Commented Oct 26, 2017 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
– Daniel Vaughn, Commented Oct 26, 2017 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
– Daniel Vaughn, Commented Oct 26, 2017 at 16:29
stackoverflow.com/questions/8250050/…, stackoverflow.com/questions/31163591/…, stackoverflow.com/questions/8949641/… — Tim Williams
– Tim Williams, Commented Oct 26, 2017 at 16:49

Tim Williams · Accepted Answer · 2017-10-26 23:15:14Z

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, 2017 at 23:15

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Daniel Vaughn Over a year ago

I added the code to the sheet and it's not working. @TimWilliams

Daniel Vaughn Over a year ago

It doesn't do anything. @TimWilliams

Tim Williams Over a year ago

So you run it and nothing happens, even if there's content in A1:A10?

Daniel Vaughn Over a year ago

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 Over a year ago

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 · Accepted Answer · 2017-11-01 20:31:00Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related