Search code examples
excelribbonexcel-2013vba

Excel VBA - call same macro from both Ribbon and AutoOpen

Having just upgraded to Excel 2013, I've moved my macros from a legacy custom toolbar to a custom ribbon menu. All works well, except for one thing. I used to have a macro that ran on AutoOpen, but could also be called manually via a button on the toolbar.

I call my macro from the ribbon using Sub myMacro(control As IRibbonControl) which works. But if I Call myMacro(control As IRibbonControl) in AutoOpen I get an "expected list separator" error. Conversely if I just Call myMacro() in AutoOpen I obviously get a "argument not optional" error. Bit of a Catch 22!

I know that I could just move my code to a third sub-routine, called by two separate macros in the ribbon and in AutoOpen, but before I admit defeat and do this I wonder if there is a way around this.

I have searched the web for a solution to this, but couldn't find anything that answered my particular query.

Thanks

Rob

Solution

  • A simple code as this will help?

    Option Explicit

Sub AutoOpen()
    Dim ctl As IRibbonControl

    myMacro ctl
End Sub

Sub myMacro(control As IRibbonControl)
    MsgBox "Hello World"
End Sub