Code to create Macro Button and Assigning Macro code

Hello:

Please refer to attached file.

i have below code which successfully create a macro button and assign the macro correctly.
This is only doing for one sheet and i need to modify the code so that it does for all sheets of the workbook.

Sub CreateButton_New()

    Dim btn As Button

    'create the button in the active sheet.
    'The 4 arguments are left, top, width and height --- i chose to base them off of cellular dimensions
    Set btn = ActiveSheet.Buttons.Add( _
        ActiveSheet.Columns(2).Left, _
        ActiveSheet.Rows(2).Top, _
        ActiveSheet.Columns(2).Width * 2, _
        ActiveSheet.Rows(1).Height * 2)

    'this is where you assing a macro to the btn
    btn.OnAction = "ShowMessageBox"

    'add a caption
    btn.Text = "Click Me"

    Set btn = Nothing

End Sub

Sub ShowMessageBox()

    MsgBox "Hello"

End Sub

Let me know if you have any questions.
Thanks.

Riz

Hi Riz,

An unrelated comment that may help you in the future after looking at your code:
To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx


Nice job Holger.

Lewis

Hi HiHoBe:

Your code works great.
Thanks a lot for such a great help.
Riz