Copying a button from one sheet to automatically run code when copied to new sheet.

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
Hi I am struggling with getting a macro to run when I create a new button on a sheet. I assign the On Action to the button and the macro runs without debug, but... the OnAction doesn't run the Macro "Test".

The Button being copied to Sheet2 is on Sheet1, then the Macro should automatically run when the button is pasted to Sheet2.

Any ideas?

Code:
Sub Macro1()

    Sheets("Sheet1").Visible = xlSheetVisible
    Sheets("Sheet1").Select
On Error Resume Next
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
  
    Selection.Copy
    
    Sheets("Sheet2").Select
    With ActiveSheet.Buttons.Add(500.5, 5.75, 90, 46.5)
        .Name = "New Button"
        .Size = 11
        .Bold = True
        .Characters.Text = "Run second Macro"
        .OnAction = "Test"
    End With
    ActiveSheet.Shapes.Range(Array("New Button")).Select
    
Sheets("Sheet1").Visible = xlSheetHidden
End Sub


Sub test()
    Sheets("Sheet2").Activate
    Cells(1, 1).Select
    MsgBox ("Macro finished, and in Cell A1")
End Sub

Thank you.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If you are going to copy and paste the button, try this


Code:
Sub Macro1()


    Sheets("Sheet1").Visible = xlSheetVisible
    Sheets("Sheet1").Select
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
  
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    
    With Selection
        .Name = "New Button"
        .OnAction = "Test"
    End With
    
    Application.Run ActiveSheet.Shapes("New Button").OnAction
    
    Sheets("Sheet1").Visible = xlSheetHidden
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,468
Messages
5,528,977
Members
409,849
Latest member
J7House1984
Top