none
Dynamically passing value to Sub using buttons RRS feed

  • Question

  • I'm trying to pass a value to a Sub using this code..


        With btn
            .OnAction = "GenerateAuditFile(" & i & ")"
            .Caption = "Generate"
            .Name = "btnsGenerateAudit " & i
        End With
    But, I'm doing something wrong. I think it's my syntax.
    Tuesday, July 30, 2019 3:19 PM

Answers

  • See if the following example helps. Note the single quotes returned with the MsgBox for .OnAction.


    Sub SetOnAction()
        Dim btn As Button
        Dim i As Long
       
        'Create the button and assign to a variable for testing
        Set btn = ActiveSheet.Buttons.Add(385.5, 30, 154.5, 31.5)
       
        i = 2   'Dummy value for testing
       
        With btn
            .OnAction = "'GenerateAuditFile """ & i & """'"
            .Caption = "Generate"
            .Name = "btnsGenerateAudit " & i
           
            MsgBox .OnAction        'For testing. Note the single quotes in the message
        End With
    End Sub


    Sub GenerateAuditFile(varArgument As Variant)
        'Clicking the button should run this sub for testing. (Argument value should be retained)
        MsgBox "GenerateAuditFile called with Argument " & varArgument

    End Sub


    Regards, OssieMac

    • Marked as answer by mongoose00318 Thursday, August 15, 2019 5:48 PM
    Wednesday, July 31, 2019 7:01 AM