Search code examples
rexcelvbaclickactivex

How to iterate pushing ActiveX command button in Excel?

I have been given a spreadsheet with an ActiveX control button to execute a complex macro that I can't see the VBA code for because it is password protected. I need to loop through 1000 input values using the ActiveX button. Is there a way to code the part where I click the button in VBA or R?

Here's what I would like to achieve:

  1. Set input cell values on SheetA
  2. Push a button labeled "RunSimulation" located on SheetA
  3. Copy output values on SheetB to a separate table
  4. Repeat

I need help with step 2.

Solution

  • You can use Application.Run here, specifying the workbook name, the sheet code module, and the click handler:

    Dim i as Long
For i = 1 to 1000
    Application.Run "'WorkbookName.xlsm'!Sheet1.RunSimulation_Click"
Next