Auto run macro on add-in load

[DaveBlakeMAAT]

Hi

I have an excel add-which is running great, however, I wish to start adding my UDFs to it. I have the code to register my UDFs but I was wondering if there is a way of automatically running the UDF registration macro when the add-in is loaded?

Many thanks!

 

[Jerry Sullivan]

You can call that code from the Workbook_Open procedure of the add-in. That Sub is run when the add-in is loaded.

 

[DaveBlakeMAAT]

Hi Jerry

Thank you for taking the time to reply to my post. Unfortunately, I am unable to get your suggestion to work.

I receive the following error:

Run Time error ' 1004':
Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command.

I have the following code in my Add-In workbook object:

Private Sub Workbook_Open()
    Call AddUDFToCustomCategory
End Sub

Then in my public function module I have:-

Option Explicit


Public Sub AddUDFToCustomCategory()
    Application.MacroOptions Macro:="InvoiceDueDate", Category:="SDHD", StatusBar:="argument1,argument2,argument3"


End Sub


Public Function InvoiceDueDate(PaymentTerms As String, InvoiceDate As Date, PaymentDays As Integer) As Date
'my function goes here
End Sub

Many thanks

Regards

Dave

 

[Jaafar Tribak]

Have you tried calling the macro in the Workbook_AddinInstall event instead of the Workbook_Open event ?

 

[Jerry Sullivan]

Jaafar, Thank you for pointing that out. I haven't used that event and I can see that it's a better fit for an operation like registering UDFs.


Dave, The call would work from the Workbook_Open event however Jaafar's suggestion is better. The error message you described probably occurred if you tried to open an add-in workbook without any other workbooks being open. Try opening a new blank workbook before opening and editing your add-in file.

btw, the code in your OP has an End Sub which should be End Function.

Public Function InvoiceDueDate(PaymentTerms As String, InvoiceDate As Date, PaymentDays As Integer) As Date
'my function goes here
End [B][COLOR="#FF0000"]Sub[/COLOR][/B]