Custom shortcut menu

[fat controller]

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]

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]

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]

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]

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]

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

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]

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

 

[arnelgp]

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

 

[fat controller]

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]

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]

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]

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

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]

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

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?

 

[SoulSeeker]

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]

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?