CODEWITHSUNDEEP
excelvba
Th3Nic3Guy
Th3Nic3Guy

Reputation: 1979

add button dynamically in excel to row

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.

Upvotes: 0

Views: 3121

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

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.

Upvotes: 3

Related Questions