add button dynamically in excel to row

Question

I am new to Excel Macro Coding and I have a requirement in which, in Excel, the user enters some numeric value in the column A and as soon as he tabs out of the screen, a new button should be generated in the same row in column G with the label same as that of the value in Column A and a macro function will be added to the button.

Also, if the user deletes the value from the row in column A, then the corresponding button should also be removed.

I tried certain forums but am not able to find the code for creating the button on focus out.

Please help.

Peter Albert · Accepted Answer · 2013-02-13 21:07:00Z

3

Try this code:

Private Const mcStrButtonAddress As String = "D3" 'The cell or range which is used for top and left position of button
Private Const mcStrMacroToRun As String = "Macro1"
Private Const mcStrButtonName As String = "Button" 'Used internally to delete button

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

    'To disable endless loop
    Application.EnableEvents = False

    'Delete button
    On Error Resume Next
    Me.Buttons(mcStrButtonName).Delete

    On Error GoTo 0
    'Insert button
    With Me.Buttons.Add( _
        Range(mcStrButtonAddress).Left, _
        Range(mcStrButtonAddress).Top, _
        Range(mcStrButtonAddress).Width, _
        Range(mcStrButtonAddress).Height)

        'Configure button
        .Name = mcStrButtonName
        .Characters.Text = Target.Value
        .OnAction = mcStrMacroToRun
    End With

    'Reenable events
    Application.EnableEvents = True
End Sub

You need to insert it in the Code module of your worksheet.

answered Feb 13, 2013 at 21:07

Peter Albert

17.6k6 gold badges69 silver badges89 bronze badges

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

2 Comments

Th3Nic3Guy Over a year ago

i, i tried the code..it worked sometimes and sometimes not...could you please help me exactly where will i place it and what it does...

Peter Albert Over a year ago

It will work every time you're changing cell A1 in your worksheet - see the first line of code. If you want a different/large range, change "A1"according to your needs

Collectives™ on Stack Overflow

add button dynamically in excel to row

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related