CODEWITHSUNDEEP
excelvba
coffent
coffent

Reputation: 11

VBA Sub Auto_Open runs but doesn't work on Excel startup

I have the following subroutine:

    VBAProject (PERSONAL.xlsb)
        Modules
            Module1
                Sub Auto_Open()
                    Dim Shortcut As CommandBar
                    Dim NewItem As CommandBarButton
                    Set Shortcut = Application.CommandBars("Cell")
                    Set NewItem = Shortcut.Controls.Add()
                    With NewItem
                        .Caption = "Insert C Comment"
                        .OnAction = "AddComment"
                    End With
                    MsgBox ("Test")
                 End Sub

which should add a new item to the right click/context menu titled "Insert C Comment", which when clicked, calls the subroutine AddComment() (also stored in Module1).

If I run the subroutine with Excel already open, everything works as expected and "Insert C Comment" is added to the context menu. However if I initially open Excel, the subroutine runs, as evidenced by the test MsgBox being displayed, but "Insert C Comment" is not added to the context menu. Any suggestions as to why?

Upvotes: 1

Views: 572

Answers (1)

coffent
coffent

Reputation: 11

[Not sure where to put this - it's too long for a comment, but it's not a definitive answer to the question I posed.]

Thanks to you both for your suggestions. I've tried them both as you'll see, but in the meantime I found that - at least now - the added menu item survives a reboot. I thought I'd earlier found it didn't. If it continues to survive, them my problem is moot - I can just add the menu item once and I'll be all set.

Nevertheless, here are the results of testing your suggestions;

I put Debug.Assert False in the code. NewItem seemed to have the right properties: Enabled=True, correct OnAction, Visible=True. However the strange thing is that after I continue code execution the menu item is created even when the module is run at startup!

I wasn't aware that Auto_Open was deprecated. I tried as you suggested putting the same code, now called Workbook_Open in the "ThisWorkbook" section of the workbook I'm testing it in. Indeed, this works and adds the menu item when the workbook is opened, and contrary to my fears once added it appears in other workbooks as well. One problem, though, is that each time I open the workbook with the code, it adds another instance of the menu item to the menu. (For some reason this doesn't occur for Auto_Open.)

So thanks again for your help. I see no reason to pursue further what now seems to be a non-problem, assuming the added menu item stays added indefinitely.

Upvotes: 0

Related Questions