Turn off Shortcut Menu on Reports (1 Viewer)

Snowflake68 · Sep 4, 2017

I am trying to find a way of disabling the shortcut menu on reports using Access 2013. There is a property called 'Shortcut Menu' on forms that allows you to set it to 'No' which switches them off but this is missing from the reports property and only allows you to specify the name of custom shortcut menu.

I do not want to untick 'Allow Default Shortcut Menus' via the system options for the Current Database as this will switch them off for everything and I need them on for some forms.

I did consider creating a custom shortcut menu with an empty menu but there doesnt seem to be a lot of help out there for creating custom shortcut menus in Access 2013. The only thing I found was some VBA that needed to reference an object library reference but I would like to avoid this if I can because there are many users of my system and this has caused me issues in the past due to users with different versions of Access.

arnelgp · Sep 4, 2017

there are two ways to do this. vba or macro.
i'll show you the macro.

create a macro:
on the macro textbox type Beep
now right click anywhere and Make a SubMacro on top of the Beep.
type anything on a description, like "menu disabled" (without quote).
save this macro as mcrBeep.

create another macro.
on the textbox type AddMenu.
type whatever you like on Menu Name.
type the macro (mcrBeep) we created before,
on Menu Macro Name.
save this macro as mcrNoShortCut.

open your report in design view.
on its Property->Other->Shortcut Menu Bar.
manually type mcrNoShortCut.
save you report, you'll have no shortcut menu
for the report. only showing "menu disabled".

Snowflake68 · Sep 4, 2017

arnelgp said:
there are two ways to do this. vba or macro.
i'll show you the macro.

create a macro:
on the macro textbox type Beep
now right click anywhere and Make a SubMacro on top of the Beep.
type anything on a description, like "menu disabled" (without quote).
save this macro as mcrBeep.

create another macro.
on the textbox type AddMenu.
type whatever you like on Menu Name.
type the macro (mcrBeep) we created before,
on Menu Macro Name.
save this macro as mcrNoShortCut.

open your report in design view.
on its Property->Other->Shortcut Menu Bar.
manually type mcrNoShortCut.
save you report, you'll have no shortcut menu
for the report. only showing "menu disabled".

Cool thanks for this. So if I want to add items to a menu how would I do this. So for example if I just wanted to allow them to right click and print?

arnelgp · Sep 4, 2017

firstly you need to create a public function in standard module for printing:

Public Function fnPrint()
Docmd.RunCommand acCmdPrint
End Function

next modify mcrBeep.
change the SubMacro Name to "Print" (without quote).
instead of Beep there on the textbox, type RunCode and fnPrint() as the Function Name.

Snowflake68 · Sep 4, 2017

arnelgp said:
firstly you need to create a public function in standard module for printing:

Public Function fnPrint()
Docmd.RunCommand acCmdPrint
End Function

next modify mcrBeep.
change the SubMacro Name to "Print" (without quote).
instead of Beep there on the textbox, type RunCode and fnPrint() as the Function Name.

Thank you. I think I can now work out what I need, plus just got to put some error trapping in for when the user clicks cancel.

Snowflake68 · Sep 4, 2017

Just for the record I added some error handling to the function. Probably not the best in error handing but it does exit smoothly when clicking on the cancel on the print window.

Code:

Public Function fnPrint()

On Error GoTo ErrorHandling

    DoCmd.RunCommand acCmdPrint
   
ErrorHandling:

Exit Function

End Function

arnelgp · Sep 4, 2017

just put On Error Resume Next to function fnPrint()

arnelgp · Sep 4, 2017

"On Error Resume Next" is not global or database-wide.
so when the function exits, normal error handling is restored.

Snowflake68 · Sep 4, 2017

Ive been trying to find out how to create an icon next to the print option. I have download the msocontrolbutton policy id numbers file for Access but cannot figure out how to use the policy Id to add the print icon to the menu.

I have tried but failed, are you able to help me again please?

arnelgp · Sep 4, 2017

sorry but you cant put any FaceID on the macro.
you have to use VBA and create the menu there.
paste in Module:

Code:

Sub CreateContextMenu()
    Const strMenuName As String = "ContextMenu1"
    On Error Resume Next
    Dim cbar As CommandBar
    Dim bt As CommandBarButton
    'delete first if already exists
    CommandBars.Item(strMenuName).Delete
    'recreate
    Set cbar = CommandBars.Add(strMenuName, msoBarPopup, , False)
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print"
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With
End Sub

run this and this "ContextMenu1" will be available
on your form/report Property->Other->Shortcut Menu Bar

Snowflake68 · Sep 4, 2017

arnelgp said:
sorry but you cant put any FaceID on the macro.
you have to use VBA and create the menu there.
paste in Module:

Code:

Sub CreateContextMenu() Const strMenuName As String = "ContextMenu1" On Error Resume Next Dim cbar As CommandBar Dim bt As CommandBarButton 'delete first if already exists CommandBars.Item(strMenuName).Delete 'recreate Set cbar = CommandBars.Add(strMenuName, msoBarPopup, , False) Set bt = cbar.Controls.Add With bt .Caption = "&Print" .OnAction = "=fnPrint()" .FaceId = 15948 End With End Sub

run this and this "ContextMenu1" will be available
on your form/report Property->Other->Shortcut Menu Bar

i have created a new module and pasted your code into it but when I try and run the code I receive a compile error. User-defined type not defined.

Any ideas what I could be doing wrong?

arnelgp · Sep 4, 2017

add Reference to Microsoft Office XX.X Object Library

XX.X is the version of office you have.

Snowflake68 · Sep 4, 2017

arnelgp said:
add Reference to Microsoft Office XX.X Object Library

XX.X is the version of office you have.

The reference was already made, see attached.
When you say run the code do you mean just click on the Run button in the VBA editor?

arnelgp · Sep 4, 2017

Not that one the other one the Object Library

arnelgp · Sep 4, 2017

Keep that plus add the Object Library

Snowflake68 · Sep 4, 2017

arnelgp said:
Not that one the other one the Object Library

Doh! what a nitwit I am, I really didnt read your post properly did I sorry.

Its working now, thanks.

Just one last question. How do I add other items to the same shortcut menu, so for example if I wanted to add the zoom option or Export to Excel option?

arnelgp · Sep 4, 2017

just add it below these line:

Code:

    ' this is your print menu item
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print"
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With

    ' new item here.. just add what you like
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "zoom"
        .OnAction = "=[your function to zoom]"
        .FaceId = [another face id]
    End With

    ' export to excel
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "Export to Excel"
        .OnAction = "=[your function to export]"
        .FaceId = [another face id]
    End With

Then run the sub again

Snowflake68 · Sep 4, 2017

arnelgp said:

just add it below these line:

Code:

    ' this is your print menu item
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print"
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With

    ' new item here.. just add what you like
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "zoom"
        .OnAction = "=[your function to zoom]"
        .FaceId = [another face id]
    End With

    ' export to excel
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "Export to Excel"
        .OnAction = "=[your function to export]"
        .FaceId = [another face id]
    End With

Then run the sub again

That simple ah! Thank you so much, you have taught me so much today and I am extremely grateful to you for your time and patience.

Snowflake68 · Sep 4, 2017

Here is the code to help others build their own shortcut menu which you need to put 'ReportsShortcutMenu' in the Shortcut Menu property of either a report or form

Code:

Sub CreateContextMenu()

'To add additional items to the Shortcut Menu you first need to create a separate function similar to fnPrint()
' then add the item code as outlined in comments below (place the new code between the existing items _
    where you want it to appear in the list)
' Then run this function to recreate the shortcut menu containing the new items.

    Const strMenuName As String = "ReportsShortcutMenu"
    On Error Resume Next
    
    Dim cbar As CommandBar
    Dim bt As CommandBarButton
    
    'delete first if already exists
    CommandBars.Item(strMenuName).Delete
    
    'recreate
    Set cbar = CommandBars.Add(strMenuName, msoBarPopup, , False)

''' add additional items by copying and amending the code below (indicated by the '*****)  _ 
using the correct face id from the AccessControls Developers Excel file
'**********StartOfMenuItemCode****************
    ' Print
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print" ' The & underlines the initial character
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With
'**********EndOfMenuItemCode******************
    
    ' Export to Excel
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "Export to Excel"
        .OnAction = "=fnExportExcel()"
        .FaceId = 11723
    End With
        
    ' Close window
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "Close"
        .OnAction = "=fnClose()"
        .FaceId = 923
    End With
    
End Sub

Snowflake68 · Sep 4, 2017

These are the separate functions used in the shortcut menu above

Code:

Public Function fnPrint()

On Error Resume Next

    DoCmd.RunCommand acCmdPrint

End Function

Code:

Public Function fnExportExcel()

On Error Resume Next

    DoCmd.RunCommand acCmdExportExcel
         
End Function

Code:

Public Function fnClose()

On Error Resume Next

    DoCmd.RunCommand acCmdClose
         
End Function

Turn off Shortcut Menu on Reports (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

..forever waiting... waiting for jellybean!

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread