How to assign macro to menu button?

[deko]

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.

 

[Bob Phillips]

Try using

ctl.OnAction = "MyFunction"

 

[deko]

ctl.OnAction = "MyFunction"

Well, that was easy. Thanks.

So I've successfully inserted a module and code into Excel from Access, and
the menu bar button I've created launches a message box.

Now I'm in the home stretch. I need to insert a user form. I'm not sure I
see any reason to put all the form's code in the new module I've inserted -
that is, it just as well could run from the form's module, could it not?
What would be nice is if I could store the form and code together as a unit
in an Access table and dump the whole thing in at once. Is this possible?

Are there any best practices for inserting code/forms/objects into Excel?

I'm wondering if I could store each procedure in a table for ease of
management. Other suggestions?

 

[Tom Ogilvy]

http://www.cpearson.com/excel/vbe.htm

 

[deko]

http://www.cpearson.com/excel/vbe.htm

That's a good resource.

So what I've got so far looks like this:

strCode = GetCode("frmOptions")
xlapp.VBE.ActiveVBProject.VBComponents.Add _
(vbext_ct_MSForm).Name = "frmOptions"
lngLn = xlapp.VBE.ActiveVBProject.VBComponents _
("frmOptions").CodeModule.CountOfLines + 1
xlapp.VBE.ActiveVBProject.VBComponents _
("frmOptions").CodeModule.InsertLines lngLn, strCode

This adds the form nicely, and I can store code snippets in a table which is
helpful, but painting the form with controls could be a challenge. Is there
an easy way to do this? Perhaps I could just make the button on the menu
bar a drop-down list of different options.

 

[Tom Ogilvy]

I am not sure why you want to build the useform with code. If so, there is
no need to import the form itself. You can add it with code as well.

http://j-walk.com/ss/excel/tips/tip76.htm
Creating a Userform Programmatically

 

[deko]

I am not sure why you want to build the useform with code. If so, there
is

no need to import the form itself. You can add it with code as well.

http://j-walk.com/ss/excel/tips/tip76.htm
Creating a Userform Programmatically

Thanks again for the tip. But I think I can skip the form.

What I've got below seems to work. What's nice is I can write and test the
code in Excel and then just cut and paste from the IDE into an Access table.
So I can catalog all kinds of options. Users select the options they want
before they create the workbook, and my code in Access will insert whatever
code is necessary to support those selections.

Public Sub AddCustomMenu()
On Error Resume Next
Dim mbMain As CommandBar
Dim mbCustom As CommandBarControl
Application.CommandBars("Worksheet Menu Bar").Controls("Custom").Delete
Set mbMain = Application.CommandBars("Worksheet Menu Bar")
Set mbCustom = mbMain.Controls.Add(Type:=msoControlPopup,
Temporary:=True)
mbCustom.Caption = "Custom"
With mbCustom.Controls.Add(Type:=msoControlButton)
.Caption = "Option1"
.Style = msoControlButton
.OnAction = "Option1Code"
End With
With mbCustom.Controls.Add(Type:=msoControlButton)
.Caption = "Option2"
.Style = msoControlButton
.OnAction = "Option2Code"
End With
With mbCustom.Controls.Add(Type:=msoControlButton)
.Caption = "Option3"
.Style = msoControlButton
.OnAction = "Option3Code"
End With
End Sub
Public Sub Option1Code()
MsgBox "Apply Option 1"
End Sub
Public Sub Option2Code()
MsgBox "Apply Option 2"
End Sub
Public Sub Option3Code()
MsgBox "Apply Option 3"
End Sub