Calling add-in module subroutine from sheet function tied to ActiveX button

[Special K]

I have an add-in file, MyAddIns.xlam, that contains a module named Module1 with a number of functions that are intended to be mapped to buttons in another spreadsheet. One example is the following:

Public Sub Test()
...
End Sub

Originally it was "Private Sub Test(), but that gave the same error as I will describe below. Also I changed the sub name to "Test" to avoid any possibility of collision with the name of the sheet function that is supposed to call this sub.

I have a spreadsheet, MyDashboard.xlsm, that contains a number of ActiveX buttons intended to be mapped to the functions defined in MyAddIns.xlam.

When I open MyDashboard.xlsm, I go to Developer->Add Ins, check the box for "MyAddIns", and then click OK.

On MyDashboard.xlsm, Sheet1, I have a button named "Generate Data". When I right click the button and select "View Code", I see the following code:

Private Sub GenerateData_Click()
    Call Test
End Sub

This seems like button GenerateData should call the Test subroutine from Module1 whenever I click it, yet whenever I do I receive the following error:

Compile Error:

Sub or Function not defined

Any ideas what the problem could be? I also tried prefixing the sub call with "Module1.", but then I get the following error:

Run-time error '424':

Object required

Any ideas?

 

[Special K]

bump

 

[offthelip]

the ms link explains what you need to do to run addin code in VBA:
https://social.msdn.microsoft.com/F...using-xla-addin-functions-in-vba?forum=isvvba

However, after picking up one your comments from your other thread, I think you would do muchj better to develop your system without using and addin. If spearate your system into the Contoller workbook with all the code, then a master workbook containing data and data workbooks, you can avoid using an addin and still make updating cod and data very easy.

 

[Special K]

the ms link explains what you need to do to run addin code in VBA:
https://social.msdn.microsoft.com/F...using-xla-addin-functions-in-vba?forum=isvvba

However, after picking up one your comments from your other thread, I think you would do muchj better to develop your system without using and addin. If spearate your system into the Contoller workbook with all the code, then a master workbook containing data and data workbooks, you can avoid using an addin and still make updating cod and data very easy.

Thanks for the link, I was able to get the add-in functions working. I will develop an alternate version without them and see which I prefer.

Also more detailed information regarding using add-in modules here (for my own future reference and anyone else who finds this thread via search):

https://www.fontstuff.com/vba/vbatut08.htm