How to create a button using a macro to run a macro

Question

I recorded a macro to help me run code I have written. The code itself works. However, I cannot seem to create a button that will run the code.

With Summary 
        .Buttons.Add(1039.8, 60.6, 66.6, 28.8).Select
        Selection.OnAction = "PERSONAL.XLSB!Clear_Error"
        Selection.Characters.Text = "Clear"
        With Selection.Characters(Start:=1, Length:=11).Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 11

End With

Summary is the worksheet.The button is created but the button itself does nothing. Also, When I tried manually assigning the macro to the button, the code still does not work. However, when I run the code, it runs smoothly.

Thanks,

G

It worked for me too, you have followed the right path, but I recommend checking the clear error macro itself. Make sure you have used sub and not function. Make sure you have only one button and right click on it and check if the macro has been assigned to it at all or not — Ibo
– Ibo, Commented Dec 13, 2017 at 23:38
Me too, I see no reason this shouldn't work, Check your spelling, check the macro itself and avoid all .Selects, .Activates, ActiveSheets and ActiveWorkbooks as @K.Davis mentions. — SilentRevolution
– SilentRevolution, Commented Dec 14, 2017 at 0:03
Have you enabled macros (check at Macro Settings)? Is your file saved as .xlsx (excel 2013 and above)? — Maki
– Maki, Commented Dec 14, 2017 at 1:34

K.Dᴀᴠɪs · Accepted Answer · 2017-12-13 23:39:42Z

1

You could try to first Set the button to a variable declared as Button.

Option Explicit

Sub Test()

    Dim Btn As Button, Summary As Worksheet
    Set Summary = ThisWorkbook.Worksheets(1)
    Set Btn = Summary.Buttons.Add(1039.8, 60.6, 66.6, 28.8)
    With Btn
        .OnAction = "PERSONAL.XLSB!Clear_Error"
        .Characters.Text = "Clear"
        With .Characters(Start:=1, Length:=11).Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 11
        End With
    End With

End Sub

I personally (as well as most of the rest of the VBA community) have a strong dislike of .Select, .Selection, .Activate, etc. So that's usually the first thing I try to get rid of when I help with issues, then move from there.

Note: I did set Summary to worksheet 1, so that may need to be modified for your needs.

answered Dec 13, 2017 at 23:39

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

How to create a button using a macro to run a macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related