Custom shortcut menu (1 Viewer)

fat controller · Nov 21, 2017

I am curious now - I currently don't have the OnAction against any of them, so they are not calling on any functions, but it is working fine??

fat controller · Nov 23, 2017

I am having to revisit this (potentially) - I tried running the database on two machines other than my usual PC, and both chucked up an error that a reference file was missing (MSO.DLL version 2.7) - looking into the references, Microsoft Office 15.0 Object Library was marked as missing, so I removed the reference - thereafter, the shortcut menus ceased to work.

One of the PC's I tried this on was running Windows 7, the other Windows 10, both fully up to date and both running Office 2010, although only the W10 machine has the full version of Access.

Whilst I could potentially copy/download this DLL file to my couple of machines, this is not going to be easy (if at all possible) for users to do without intervention from someone with Admin rights, and this I would like to avoid.

Is there a way round this, or am I going to be better to do something with the ribbon? If so, is the ribbon easily manipulated?

isladogs · Nov 23, 2017

MSO.dll is a system file installed as part of Office so it should always be present on users' computers.

However, the path may vary depending on:
a) Office version
b) Office & Windows bitness (32/64)

So you just need to browse for it

I don't have Office 2013 so I can't tell you the path but this may help

a) Office 2010 32-bit in Windows 32-bit:
C:\Program Files\Common Files\Microsoft shared\OFFICE14\mso.dll

b) Office 2016 32-bit in Windows 32-bit OR Office 2016 64-bit in Windows 64-bit
C:\Program Files\Common Files\Microsoft shared\OFFICE16\mso.dll

c) b) Office 2016 32-bit in Windows 64-bit
C:\Program Files (x86)\Common Files\Microsoft shared\OFFICE16\mso.dll

HTH

On the highly unlikely event it really is missing, an Office repair should fix it (or if necessary a reinstall)

Gasman · Nov 23, 2017

As a novice, I'd say creating your own ribbon is much harder/more laborious after having watched some of this guys excellent videos for creating your own ribbons.

https://www.youtube.com/watch?v=Zt3F4_Z5LIw

Your approach seemed very easy to implement.?

Mine is in C:\Program Files (x86)\Common Files\microsoft shared\OFFICE15 for Office 2007?, but I thought you said your users would be using the runtime version

fat controller · Nov 23, 2017

Thanks guys - users would be running the runtime version, and whereas most of them would have some form of Office installed, I cannot guarantee that nor can I rely on them (or our IT guys) to be able to locate this DLL and/or copy it to the correct location.

Somewhat more concerning is that the two other PC's I have tried this on were my own work PC (runtime environment, running Office 2010, but not full Access version), and my own laptop (full version of Office Pro 2010. So 66% of the machines I have tried this on so far do not work.

I will take a look at the ribbon video later and see if it is something that is do-able; I don't mind taking a wee bit of time if this solves a problem, not least as it is a learning experience going forward.

arnelgp · Nov 23, 2017

here is a module that will fix
missing reference (MS Office Lib only)
and put the correct reference (version).

Code:

Option Compare Database
Option Explicit

Public Sub fixReference()
    Dim ref As Access.Reference
    Dim strFullPath As String
    Dim strFile As String
    Dim lngPos As Long
    For Each ref In Access.References
        If ref.IsBroken Then
            strFullPath = ref.FullPath
            ' check if one of missing is MS Office Lib
            lngPos = InStrRev(strFullPath, "OFFICE")
            If lngPos <> 0 Then
                ' if this is Office Lib, remove the reference
                Access.References.Remove ref
                
                ' separate the file from the path
                strFile = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
                
                'below is the path
                strFullPath = Left(strFullPath, Len(strFullPath) - Len(strFile))
                
                'remove the word "OFFICE" from the path
                'and replace it with Local Version installed
                strFullPath = Left(strFullPath, lngPos - 1) & "OFFICE" & Int(version()) & "\" & strFile
                
                'add reference to this version
                Access.References.AddFromFile (strFullPath)
            End If
        End If
    Next
End Sub

fat controller · Nov 23, 2017

You are a genius! If I put that in a standard module, what event makes the code fire?

arnelgp · Nov 23, 2017

you have to run it manually. click anywhere inside the sub and press F5.

fat controller · Nov 23, 2017

How would I be able to do that on a machine that has the runtime environment - it does not give access to any of the code?

isladogs · Nov 23, 2017

I haven't tried this code but you could create an autoexec macro and call it from there.

However, doing so would cause it to run every time you open the app.
Perhaps a boolean field could be created in a table so it only runs when the value is false.
For this idea to work, set the value of the Boolean field true just before the routine closes.

Perhaps OTT but you could then add a check to confirm the file exists at the specified location and if not run the code anyway ...

fat controller · Nov 24, 2017

Sounds sensible, however the error currently shows as the database is first opening, so I am extremely unsure as to how I would capture the error and populate any boolean field?

isladogs · Nov 24, 2017

OK this should be treated as aircode ...
As I said I haven't tested arnel's code properly as I'm reluctant to mess up my own mso.dll reference!

1. Add a boolean field to a table e.g. OfficeRefCheck in tblProgramSettings. Set its value as False

2. Change FixReference from a Sub to a Function so it can be called from Autoexec macro

3. Create an Autoexec macro similar to this so it will run at startup

The 2 message boxes are just for testing. You'll probably want to remove them

4. Modify arnel's code as below - changes in RED

Code:

Public [COLOR="Red"]Function[/COLOR] FixReference()

    Dim ref As Access.Reference
    Dim strFullPath As String
    Dim strFile As String
    Dim lngPos As Long
    
    For Each ref In Access.References
       [COLOR="red"] If ref.Name = "Office" Then[/COLOR]
            If ref.IsBroken Then
                strFullPath = ref.FullPath
                ' check if one of missing is MS Office Lib
                lngPos = InStrRev(strFullPath, "OFFICE")
                If lngPos > 0 Then
                    ' if this is Office Lib, remove the reference
                    Access.References.Remove ref
                    
                    ' separate the file from the path
                    strFile = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
                    
                    'below is the path
                    strFullPath = Left(strFullPath, Len(strFullPath) - Len(strFile))
                    
                    'remove the word "OFFICE" from the path
                    'and replace it with Local Version installed
                    strFullPath = Left(strFullPath, lngPos - 1) & "OFFICE" & Int(Version()) & "\" & strFile
                    
                    'add reference to this version
                    Access.References.AddFromFile (strFullPath)
                End If
                
                [COLOR="red"]If lngPos > 0 Then
                    'set the boolean flag = true so the function won't run in future
                    CurrentDb.Execute "UPDATE tblAppSettings SET tblAppSettings.OfficeRefCheck = True;"
                End If[/COLOR]
                
            [COLOR="red"]Else
                'set the boolean flag = true so the function won't run in future
                CurrentDb.Execute "UPDATE tblAppSettings SET tblAppSettings.OfficeRefCheck = True;"[/COLOR]
           End If
       
        [COLOR="Red"]End If[/COLOR]
    Next
    
End Function

NOTE: The function should run once, fix the reference if broken (not tested) & update the boolean field whether ref is broken or not

I would DEFINITELY add error handling to the above before it is used as if it isn't correct as written it could prevent your db starting

Gasman · Dec 1, 2017

FC,

Would you have a code for the equivalent of 'Send To' please, so that I could email a report directly without having to write any extra code, or direct me to where all these codes are held?

TIA

fat controller said:
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:

Code:

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?

SoulSeeker · Jul 11, 2021

arnelgp said:
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.

Excellent suggestion. I do have a question, it seems that I have to do the F5 every time I log in to the database. Is there a way so I do not have to do F5 each time I log in?

theDBguy · Jul 11, 2021

SoulSeeker said:
Excellent suggestion. I do have a question, it seems that I have to do the F5 every time I log in to the database. Is there a way so I do not have to do F5 each time I log in?

Hi. Welcome to AWF!

This is a four-year old thread. May I suggest starting a new one and perhaps include a link to this one for reference?

Custom shortcut menu (1 Viewer)

fat controller

Slightly round the bend..

fat controller

Slightly round the bend..

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

fat controller

Slightly round the bend..

arnelgp

..forever waiting... waiting for jellybean!

fat controller

Slightly round the bend..

arnelgp

..forever waiting... waiting for jellybean!

fat controller

Slightly round the bend..

isladogs

MVP / VIP

fat controller

Slightly round the bend..

isladogs

MVP / VIP

Attachments

Gasman

Enthusiastic Amateur

SoulSeeker

New member

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread