I have this code in the "ThisWorkbook" Module:

Private Sub Workbook_Open()
Call Module1.MenuBar
End Sub

And this code in Module1:

Private Sub MenuBar()
Dim ctl as CommandBarButton
Application.CommandBars("Worksheet Menu
Bar").Controls("MyOptions").Delete
Set ctl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
ctl.Caption = "&MyOptions"
ctl.Style = msoButtonCaption
'ctl.OnAction = MyFunction
End Sub

This code adds a "MyOptions" button to the menu bar when the workbook is
opened. The reason I want the button located on the menu bar is because
it's always visible regardless of what tool bars are displayed, and
regardless of what worksheet is active.

I want the button to open a user form ("frmOptions") when the "MyOptions"
button is clicked. All the code behind frmOptions will be in Module1 and
will enable the user to perform various filtering, formatting and what-if
scenarios.

The OnAction event (commented out above) runs when the workbook is opened
(which I don't want) and will not run on click (which I do want). If I
right click on the menu bar, select Customize, then right click on the
"MyOptions" button after the Customize window appears, I can assign a macro,
and the macro will run on click. But I need to be able to assign the macro
programmatically via automation from Access - and I want to assign the on
click event of the button to a function in Module1 (rather than a macro).
Is this possible?.

Any suggestions on how to do this?

Thanks in advance.