0

I have a workbook Wbk1 where a sheet has an ActiveX button. I want to run the button's associated Sub from another workbook,Wbk2. It's not practical to just copy the Sub's code into Wbk2 because it in turn calls a bunch of functions from Wbk1. I tried the following:

Sub pushButton()

Dim obj As OLEObject
Dim btn As MSForms.CommandButton

For Each obj In Wkb1.Sheets("testSheet").OLEObjects
  If TypeOf obj.Object Is MSForms.CommandButton Then
    set btn=obj.Object
    debug.print btn.Caption 'used to test whether or not For loop is picking up button
    obj.Activate
    SendKeys " "
  End If
Next

End Sub

It's not only an inelegant SendKeys() hack, but it doesn't work; the button gets focus but it doesn't get pushed. I know that the For loop is correctly iterating through the objects, including the CommandButton, because I can pick up the caption for the button. How can I click on this button (and thereby run its private Sub) from Wbk2?

EDIT: The actual filename in question is in the format 123A_1.0-2.0.xlsm. I think the periods are causing some trouble with regard to the solutions posted in comments and responses below, because when I remove the periods these techniques are successful.

sigil
  • 9,370
  • 40
  • 119
  • 199
  • 1
    If you know the name and location of the procedure then you can run it using `Application.Run` eg: `Application.Run "temp.xlsm!Sheet1.CommandButton1_Click"` **EDIT** snap! – Tim Williams Oct 31 '12 at 00:29
  • @TimWilliams, thanks, this helps and when I run it with the filename "temp.xlsm", it works, but my actual file name is causing some problems because it has periods in it; see my response to nutsch's answer below. Sorry for not including this in the question as originally posted. – sigil Oct 31 '12 at 18:27
  • 1
    see here: http://stackoverflow.com/questions/2136768/using-application-run-in-excel-vba-when-workbook-name-contains-spaces You need to put single-quotes around the filename. – Tim Williams Oct 31 '12 at 20:45

1 Answers1

1

How about just 

Application.Run (wb1.Name & "!MacroName")
Kjuly
  • 34,476
  • 22
  • 104
  • 118
nutsch
  • 5,922
  • 2
  • 20
  • 35
  • This helps, although I also need to include the sheet name where the button is, like `Application.Run "Wbk1!Sheet1.MacroName`. The problem is that my actual workbook name has periods in it, e.g. Wbk1.0.xlsm. Neither `Wbk1.0!Sheet1.MacroName` nor `Wbk1.0.xlsm!Sheet1.MacroName` successfully runs the procedure, as the first gives a "file cannot be found" error and the second gives a "Method 'Run' of object '_Application' failed" error. – sigil Oct 31 '12 at 18:26