Programmatically Add Buttons to Sheet on WorkbookOpen

Question

I'm attempting to load some buttons onto spreadsheets which include "Report" in the name using a script from a personal macro workbook. I've verified that the script is actually running with the MsgBox, and have also verified that the script successfully adds buttons when I manually execute it, however it's failing to load the buttons onto the sheet when the file is opened.

I've tried adding Worksheets(1).Activate as well as changing ActiveSheet to Worksheets(1) as I thought that maybe it's not working because the sheet wasn't active yet, but that didn't help.

Option Explicit
Private WithEvents app As Excel.Application
Private Sub app_WorkbookOpen(ByVal wb As Workbook)
    If InStr(wb.FullName, "Checksheet") > 0 Then
        'stuff happening
    ElseIf InStr(wb.FullName, "Report") > 0 Then
        MsgBox "Good"
        ActiveSheet.Buttons.Delete
        Dim btn As Button

        With Range("D2")
            Set btn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
            With btn
                '.OnAction = "BtnTest.secondTest"
                .Caption = "Verify First Report"
                .name = "Btn1"
            End With
        End With

        With Range("D4")
            Set btn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
            With btn
                '.OnAction = "BtnTest.secondTest"
                .Caption = "Verify Second Report"
                .name = "Btn2"
            End With
        End With

    End If
End Sub

Private Sub Workbook_Open()
    Set app = Me.Application
End Sub

Did I understand correctly: Opening files which include "Report" in their name should trigger the code, but those files should not contain any VBA code themselves? The relevant code should only be in your personal macro workbook? — Asger, Jul 12 '19 at 14:02
Correct. Everything is written in and executed from the macro workbook (making the event handler necessary - see https://stackoverflow.com/a/56671540/8722088). — Tock, Jul 12 '19 at 14:06
@Rory Oh jeez, it really was that easy. I even used that in the first `If` code block. Feel free to answer so I can credit you. — Tock, Jul 12 '19 at 14:09
Verify `MsgBox ActiveSheet.Name` and `MsgBox ActiveSheet.Parent.Name`? It's a good idea (a best practice, really) to [not rely on Activate/Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — David Zemens, Jul 12 '19 at 14:11

score 2 · Accepted Answer · answered Jul 12 '19 at 14:11

2

Use wb.Activesheet or wb.Worksheets(1)

Your code is in the ThisWorkbook module so Activesheet is implicitly ThisWorkbook.Activesheet rather than Application.Activesheet

answered Jul 12 '19 at 14:11

Rory

32,730
5
32
35

Programmatically Add Buttons to Sheet on WorkbookOpen

1 Answers1