Custom shortcut menu

[fat controller]

I have a database that when deployed will be used mainly via the Access Runtime environment, and therefore the features to export reports to Excel, Word etc will not be available.

In the past, I have used a custom shortcut menu, code as follows:

Dim MenuName As String
Dim CB As CommandBar
Dim CBB As CommandBarButton

MenuName = "vbaShortcutMenu"

On Error Resume Next
Application.CommandBars(MenuName).Delete
On Error GoTo 0

Set CB = Application.CommandBars.Add(MenuName, msoBarPopup, False, False)

Set CBB = CB.Controls.Add(msoControlButton, 19, , , True)
CBB.Caption = "Copy..."
CBB.FaceId = 19

Set CBB = CB.Controls.Add(msoControlButton, 22, , , True)
CBB.Caption = "Paste..."
CBB.FaceId = 1436

Set CBB = CB.Controls.Add(msoControlButton, 11725, , , True)
CBB.Caption = "Export to Word..."
CBB.FaceId = 42

Set CBB = CB.Controls.Add(msoControlButton, 11723, , , True)
CBB.Caption = "Export to Excel…"
CBB.FaceId = 263

Set CBB = CB.Controls.Add(msoControlButton, 12499, , , True)
CBB.Caption = "Save as PDF…"
CBB.FaceId = 3


Set CB = Nothing
Set CBB = Nothing
End Sub

So, I thought I had nothing more to do than pinch that code, and use it in the new database...... initially, I got a runtime error (object not defined) with the line Dim CB as CommandBar hightlighted.

I added in Microsoft Office 15 Objects as a reference, and thereafter the code compiled fine - however, it does not appear to work.

I have stepped into the code, and it runs through as though it is doing its thing, but when I right click on the report in Preview, I get the 'normal' shortcut menu.

Any ideas?

 

[arnelgp]

you need to add OnAction and set this
to a Public Function, eg:

CBB.Caption="Save as PDF"
CBB.FaceId=3
CBB.OnAction="fnSaveAsPDF()" 'create a public function for the Action
...
...
...


' must be in a standard module
Public Function fnSaveAsPDF()
docmd.OutputTo acOutputReport,"myReport",acFormatPDF, Environ("UserProfile") & "\Documents\ArnelGP.pdf", True
End Function

 

[fat controller]

I don't remember having to do that before - has something changed within Access with respect to how this works?

Your suggestion looks spot on though, and will certainly do what I need it to - thank you

 

[isladogs]

Command bars were replaced when the ribbon was introduced back in A2007.
For a more general solution that works as you had before you'd need to create your own ribbon but its more tricky than the old method.

 

[fat controller]

Ah, well as long as I can have the export to PDF, word and excel options I am happy. Thanks

 

[fat controller]

Still doesn't work - no matter, I will just put a command button on the dashboard to export directly to excel. Nice of them to remove our ability to make things easier......

Thanks anyway guys

 

[arnelgp]

hi miss... why the early resignation?!
what is not working, and what is the function you have so far?
did the right-click menu appearing, then there is no problem on that area.
only problem with its functionality.

 

[fat controller]

I don't have a lot of time to play with, and this was purely a nice to have

The shortcut menu does not appear at all, despite the code running through - I have it as an OnLoad event in the report

 

[fat controller]

OK, after a bit of faffing around, it now fires the save as excel function as the report opens.

I think the problem lies with this line - Application.CommandBars(MenuName).Delete

It doesn't delete the embedded shortcut menus because the CommandBars function is depreciated?

 

[arnelgp]

err... you said it will be deployed as Runtime.
and beside, you don't call it on the Report's Load event
you open your Report in design View.
on its Property->Other->Shortcut Menu Bar
put the name there (on Post #1, you named it: vbaShortcutMenu)

 

[fat controller]

It will be deployed as runtime eventually, but I am currently running it with 2010

OK, I have put vbShortcutMenu in the Shortcut Menu Bar, where then do I tell it to load that shortcut menu bar? The last time I did this (admittedly some years ago), the OnLoad event had CreateShortcutMenu which then created the desired shortcut menu.

We are getting somewhere though - I now get the send to Excel as the only shortcut menu (it is the first one)

 

[arnelgp]

you have the Sub there that create the shortcutmenu. you run it by clicking inside the sub and pressing F5. when you do that, the shortcutmenu becomes part of your db. so no need to run the sub again, unless you are adding another menu items and you want to update your shortcutmenu. putting it on the Load event of the report is redundant.


it is not obsolete. and the ribbon is not the replacement for shortcut menu. although you can make shortcutmenu using 2 macros, but you cannot put any faceid (the icon) on them.

 

[fat controller]

Where do I actually put the sub then, as it was in the code for that one report, and I would like it to be available to all reports?

 

[arnelgp]

you don't call it on the Report/Form load. and you don't need to

you put the code in standard module.
if you need to add new item for menu edit the sub and run it (F5).


once the shortcutmenu is created it becomes part of your database
so no need to re-run and re-create it again and again.


it is available on all reports/form, put you must explicitly
put it in its Property on design view, just like you did today.

 

[fat controller]

Ah, I get it now - make it a public sub in a module. Works fine now, thank you for your patience, and my apologies for being thick.

 

[Gasman]

Arne,
I dare say a person like yourself, could write some code that would add that menu to all reports in a database?, rather than having to edit them one by one.

Thank you for that tip, it could come in useful for me at some stage.

you don't call it on the Report/Form load. and you don't need to

you put the code in standard module.
if you need to add new item for menu edit the sub and run it (F5).


once the shortcutmenu is created it becomes part of your database
so no need to re-run and re-create it again and again.


it is available on all reports/form, put you must explicitly
put it in its Property on design view, just like you did today.

 

[fat controller]

Interestingly, I didn't put an action in for Word, Copy or Paste - yet they are all working.....

 

[fat controller]

And commenting out the functions for the actions, they all still work too

 

[arnelgp]

Gasman, you already know the answer to that.
loop through the Report/Form Collection.
open each in design view.
get an instance of the report object
set its shortcutmenubar property, eg:





Dim r As Report
DoCmd.OpenReport "table1", acViewDesign
Set r = Report_Table1
Report_Table1.ShortcutMenuBar = "shortcutmenuname"
Set r = Nothing
DoCmd.Close acReport, "table1", acSaveYes


***
you can only do this if you are not deploying the
final runtime version.

 

[arnelgp]

And commenting out the functions for the actions, they all still work too

yes, it will, so long as you don't run the Sub again with OnAction commented out, since it is Deleting and Re-creating the shortcutmenu.