CODEWITHSUNDEEP
vbaexcel
GCC
GCC

Reputation: 295

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

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

Upvotes: 0

Views: 199

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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.

Upvotes: 1

Related Questions