VBA Excel error with Button objects .OnAction

user_15072527 0
Viewed 169

My job we have a lot of reports that's generated. Using a modular approach I'm trying to set up a Excel workbook that dynamically creates sheets based on what report that's picked from Sheet("MainMenu")

Anyways, in the dynamic sheet I have the following

Public Sub OFFD_Report_Parse()
   Init 'just declares workbook and worksheet globals

   If shtTCL.Visible = False Then shtTCL.Visible = True
   shtTCL.Activate
   shtMenu.Visible = False
        
   Dim rngBtn1, rngBtn2, rngBtn3, rngBtn4 As Range
   Dim btn1, btn2, btn3, btn4 As Button

   Set rngBtn1 = shtTCL.Range("B10")
   Set btn1 = shtTCL.Buttons.Add(rngBtn1.Left, rngBtn1.Top, rngBtn1.Width, rngBtn1.Height)
   With btn1
    .OnAction = "TCL1"
    .Caption = "Copy 1"
   End With

When ran I get Run-time error '1004' Unable to set the OnAction property of the Button Class

The sub it's calling:

Sub TCL1()
     Clipboard ("SELECT ORDER WITH FLAG.DELETE = " & Chr(34) & "Y" & Chr(34) & Chr(13))
     CopyMsg 
End Sub

Is in the same module. I've used this exact method of code in another excel file with no issues which is really making me stumped.

Thank you in advance!!!!

vba excel
1 Answers

There are two things you need to do to solve your problem.

  1. The code must be in a standard module, not the sheet-behind
  2. You can't have a numeric suffix on the action

I also notice that you're trying to dim a bunch of variables instead of using an array or collection. Only dim one variable per line. Each variable needs the As statement. Those that are omitted are declared as a variant by default.

If you use a collection you can dynamically generate the array without knowing how many elements it will have up front.

Where I explicitly call out the index of the collection (0) you could instead use a loop and a loop counter.

EXAMPLE:

Option Explicit

Public Sub OFFDReportParse()
    Init 'just declares workbook and worksheet globals
    
    If shtTCL.Visible = False Then shtTCL.Visible = True
    shtTCL.Activate
    shtMenu.Visible = False
         
    Dim buttonRanges As Collection
    Dim newButtons As Collection
    
    buttonRanges.Add shtTCL.Range("B10")
    newButtons.Add shtTCL.Buttons.Add(buttonRanges(0).Left, buttonRanges(0).Top, buttonRanges(0).Width, buttonRanges(0).Height)
    With newButtons(0)
        .OnAction = "TCLaction"
        .Caption = "Copy 1"
    End With
End Sub

Public Sub TCLaction()
     Clipboard ("SELECT ORDER WITH FLAG.DELETE = " & Chr(34) & "Y" & Chr(34) & Chr(13))
     CopyMsg
End Sub
user_81738700
Related