CODEWITHSUNDEEP
excelvba
Tock
Tock

Reputation: 61

Programmatically Add Buttons to Sheet on WorkbookOpen

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

Upvotes: 2

Views: 61

Answers (1)

Rory
Rory

Reputation: 34045

Use wb.Activesheet or wb.Worksheets(1)

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

Upvotes: 2

Related Questions