2

I am writing a VBA macro that calls some macros from other Excel workbooks. These Macros are protected and I do not have the ability to see or modify their code. In one of the macros, an InputBox is called.

Is there a way to automatically trigger the OK button so that the InputBox does not load up (or pops up and then closes without prompting the user)? I can live with the default value for the input box to be used (though I'd also like to modify it if possible).

Please let me know if more information is needed - thanks in advance.

pnuts
  • 58,317
  • 11
  • 87
  • 139
benc-m
  • 21
  • 1
  • Are those protected workbooks Excel 97-2003 (.xls) or 2007 and later (.xlsm)? – Julien Marrec Dec 20 '13 at 14:49
  • My macro is running from an Excel 97-2003 file, but the macro that is protected is from an .xlsm file. – benc-m Dec 20 '13 at 14:51
  • Arg :( Cracking the password of a 97-2003 is easy, not so much for a 2007+ file. – Julien Marrec Dec 20 '13 at 14:53
  • Unfortunately even if I could crack the password, I am not allowed to modify the file. I have to submit the protected sheet back to the customer and they'd notice if there was a change made to it. – benc-m Dec 20 '13 at 14:57
  • Don't think you can suppress the inputbox without modifying the code ... there is a dirty way of using `SendKeys` ... but I would not recommend it. – Pankaj Jaju Dec 20 '13 at 14:58
  • 1
    http://stackoverflow.com/questions/15922300/passing-value-to-excel-inputbox-from-vb-net/16112473#16112473 – Siddharth Rout Dec 20 '13 at 15:00
  • I tried using SendKeys (called right before I call the protected macro) and it seems to be working. I'm going to try what Siddharth suggested (a bit cleaner). Thank you all! – benc-m Dec 20 '13 at 15:07
  • Siddharth - I'm finding that once I call the protected macro my code is interrupted so that I can't execute my code until after the input box is closed. – benc-m Dec 20 '13 at 15:13
  • @benc-m - your macro is waiting till another macro is ends. It's normal behaviour of MS Excel. To go around it, please see my answer. – Maciej Los Dec 30 '13 at 11:17

1 Answers1

1

Have a look at example:

Sub MyMacro()
   Call MacroInDifferentWorkbook
   'here code to catch InputBox
End Sub

If you're trying to do that in that way, the answer is NO, you can't catch InputBox from MacroInDifferentWorkbook. It was well explained here:

Simulating Multithreading in VBA using Excel
Multithreaded VBA – An Approach To Processing Using VBScript

Maciej Los
  • 8,468
  • 1
  • 20
  • 35