excelvbacommandbuttonactivexobject

Compile error: Argument not optional on CommandButton_Click

I've added a Command Button to one of my workbooks to trigger execution of a macro. The attached code is very simple:

Private Sub CommandButton1_Click()
Call Evaluate
End Sub

However, when I click the button, I get a Compile error: argument not optional on Private Sub CommandButton1_Click(). Why is this happening? According to Microsoft's support website this should work.

This button is the only button in the entire workbook and the code is the only code attached to that worksheet. The called macro is in Module1 of the same workbook and does not require any arguments.

Solution

  • The issue is with the method Evaluate. What do you want to evaluate? See here an example:

    Private Sub CommandButton1_Click()
    Range("A1") = Evaluate("sin(pi()/2)")
End Sub

    See here more documentation here: Worksheet.Evaluate method (Excel)

    Also, the code for the 'Form Controls' should be in a Module and the object is called just Button. On the other hand, the code for the ActiveX Controls is in the Sheet's code area, it is private, and the name of the function its name is Command Button. In that case, I think that you use ActiveX Button, and you put your code in the Module area instead of the Sheet code area.