calling Excel add-in sub from worksheet code 1

[RobBroekhuis]

I have an AddIn application which manipulates user input files, and creates output workbooks. On one of the output file's worksheets, I place a button from the controls menu bar, and in the worksheet's code, I have:

Private Sub btnUpdate_Click()
   ExtractPlanValues
End Sub

ExtractPlanValues is a public sub in my AddIn. Excel can't find the sub and generates an error when I click the button. Is there any way I can call an AddIn's public subs from outside of the AddIn's code?
Rob

 

[rmikesmith]

Rob,

One of two ways:

Private Sub btnUpdate_Click()
   Application.Run "AddInName.xla!ExtractPlanValues"
End Sub

or, you can call the sub directly (as in your example) if you first set a reference to the AddIn: In the VB Editor, Tools / References.

HTH
M. Smith

 

[RobBroekhuis]

Thanks, that did the job. I forgot at first to enclose my AddIn's name (which includes spaces) in single quotes, but after I figured it out, it worked. I'm glad to be able to remove my workaround, which was to add an invisible menu command to the custom menu built by my AddIn, and have the button click event handler execute the menu item. Worked like a charm, but not the best example of high-clarity programming...
Rob