excelvbabutton

OnAction VBA Excel not able to call macro

I've made a vba macro to collect BoE data.

As part of my macro I have a button that removes a row of data from one sheet, alongside an entire other sheet.

    Dim btn As button
    Dim btnName As String
    Dim actionStr As String
    btnName = buttonText & "_" & sheetName ' Use sheet name for unique identifier
    Set btn = ws.Buttons.Add(ws.Cells(buttonRow, buttonColumn).Left, ws.Cells(buttonRow, buttonColumn).Top,       ws.Cells(buttonRow, buttonColumn).Width, ws.Cells(buttonRow, buttonColumn).Height)

    
    With btn
        .OnAction = "'" & buttonMacro & " """ & sheetName & """'"
        .Caption = buttonText
        .Name = btnName

This was all working perfectly fine yesterday, but today when trying to run it I get the following error:

enter image description here

I have literally no idea what could be causing this error at this point, and would appreciate any advice - thanks!

I've tried every possible way of writing the .OnAction line with literally no success - leading me to think the issue might lie elsewhere. Just strange given it was working yesterday

Solution

  • As mentioned in my comment, this seems like a bug. It's the first time I've seen this trick of enclosing the name of the macro and the parameter between single quotes. If you had asked me, I would have said that it's not possible to pass an argument via OnAction for a worksheet button. This technique you used is an interesting hack, but it's fragile since closing and re-opening the Excel file breaks the button.

    I was able to reproduce this bug systematically by doing the following:

    1. Create a new workbook
    2. Add a new module and paste this simplified code
    3. Run CreateButton
    4. Test the button to make sure it works
    5. Save and close the workbook
    6. Re-open the workbook and click the button
    7. You should then see the same error message as OP

    (Microsoft 365 MSO (Version 2406 Build 16.0.17726.20078) 64-bit)

    A workaround would simply be to not pass any arguments, but use Application.Caller to extract the name of the sheet from the name of the button:

    
Sub CreateButton2()
   
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)
   
    Dim Btn As Button
    Set Btn = ws.Buttons.Add(0, 0, 100, 25)
    
    With Btn
        .OnAction = "TestMacro2"
        .Caption = "Test2"
        .Name = "TestButton2_" & ws.Name
    End With

    Set Btn = Nothing

End Sub


Sub TestMacro2()

    MsgBox Right(Application.Caller, Len(Application.Caller) - InStr(Application.Caller, "_"))

End Sub