Creating custom CommandBarControl objects for a shortcut menu

[emorris1000]

Howdy

I finally got tired of having 100 macros managing my different custom shortcut menus, and decided to figure out how to generate the shortcut menus programatically (because lets be honest, Macros are the devil.)

I found a good tutorial here that I suggest anyone trying to do this should read:

http://blogs.office.com/b/microsoft...t-menu-for-a-form-form-control-or-report.aspx


and was able to create some basic shortcut menus like the following:

Sub CreateCopyShortcutMenu()
Dim cmbshortcutmenu As Office.CommandBar
 
    Set cmbshortcutmenu = CommandBars.Add("CopyShortcutMenu", _
                        msoBarPopup, False, False)
 
    'ID 19 adds copy command
    cmbshortcutmenu.Controls.Add Type:=msoControlButton, Id:=19

End Sub

Nice and simple, now I have a copy command. But the problem is that I also need some custom commands. Most (possibly all?) of these would be function calls.

Anyone know how to do this?

 

[MarkK]

You want to set the OnAction property of the CommandBarControl object to the name of public function (maybe a sub would work) in a standard module, and then that routine is run when the control is clicked.

Consider code like this, where CustomPopup is a previously defined CommandBar. . .

   Set cbc = CustomPopup.Controls.Add(msoControlButton)
   With cbc
      .Caption = "Show Wizard . . ."
      .Style = msoButtonIconAndCaption
      .OnAction = "ShowMyWizard"
      .FaceId = 625
      .BeginGroup = True
   End With

. . . so when you click that control, this routine would run . . .

Public Function ShowMyWizard()
   DoCmd.OpenForm "MyWizard"
End Function

hth

 

[ChrisO]

If you want to drive the Shortcut Menus from table data there is a late bound demo here:-

http://www.access-programmers.co.uk/forums/showthread.php?t=202673

Chris.

 

[emorris1000]

Ok, that works great! Thanks much. I'll have to check out the late binding method at home. I can't download stuff at work.

Quick followup. Does it have to be a sub or could I just write out the code I wanted executed? Some of them may be pretty simple and wouldn't really justify a sub (I like to keep code minimal/clean.)

ed: for exapmle I tried

.onaction = "msgbox Application.CurrentObjectName" and that didn't work.