1

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.

Mon Mo
  • 180
  • 7
  • 3
    Alternate option: [Crack the password](https://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project) – BigBen Jan 02 '20 at 21:44
  • 1
    As convenient as that would be, that would violate the trust of my university collaborator who wrote the password-protected VBA code, and he would likely withdraw his support from our project if we went that way. – Mon Mo Jan 02 '20 at 21:59
  • 1
    I suggest that since the author is a collaborator, you ask him/her to add the request to the code. – GMalc Jan 03 '20 at 01:10

1 Answers1

1

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
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • That gets me closer but now I get the error `"unexpected string constant in "Application.Run " "WorkbookName.xlsm'!SheetA.RunSimulation_Click""` I tried inserting the full file path before the workbook name as well, and that got the same result. – Mon Mo Jan 03 '20 at 00:05
  • Why the extra quotes? Single quote before the workbook name, and only one quote at the end. – BigBen Jan 03 '20 at 00:48
  • 1
    Thank you, this works on a new button I created and has made me realize there are some issues with the button I was originally trying to push that are unrelated to this topic. – Mon Mo Jan 11 '20 at 00:32