• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 928
  • Last Modified:

MS Office Custom Menu

Grettings;

I have a subform on an Access form.  When you right click on the record selector of the datasheet, you currently get a standard right click popup menu (Form Datasheet Row)that lists the available actions (Cut, Paste, Delete Record, etc).  Here is what I want to happen:

1) When I right click on the subform record selector, I do not want to see the original built-in right-click menu.
2) I want to see a custom menu that lists a completely different list of options.
3) I want the new custom menu to be set to "Temporary", so that it unloads at progam shut-down.
4) I want the original right-click menu to still be available at a later date.

I can write something like the below listed code to add a new popup menu, but I do not know how to make it replace the existing built-in menu.  If I name my new custom menu the same name as this existing right-click menu, then I obviously get an error since a menu by the same name already exists.

So how do I assign/build a new temporary right-click menu in place of the original right click menu?

Thanks,
Greg


Sub AddFormRowMenu()
Dim oMenu As Office.CommandBar

Set oMenu = Application.CommandBars.Add("Form Datasheet Row", msoFloating, False, True)
oMenu.Visible = True

End Sub

0
CascadeGeneral
Asked:
CascadeGeneral
  • 9
  • 4
  • 2
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

Everything you need is here - no code ... see this link:

http://www.jamiessoftware.tk/articles/menubars.html
       
Not sure it that article mentions it, but ... when you are in the Customize mode (dialog) ... if you hold down the Control key, you can drop and drag (ie, copy) menu items from one place to another ... handy for making a new menu that is similar another one.

Here is a related link also:

http://office.microsoft.com/training/Training.aspx?AssetID=RP010036621033&CTT=6&Origin=RC010036361033

http://msdn2.microsoft.com/en-us/library/aa210698(office.11).aspx

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I've been using this technique for years ... 100's of custom menus.

mx
0
 
CascadeGeneralAuthor Commented:
Maybe I have an unfounded concern, but I'll pass it on anyway.

Is it possible for the custom menus to get screwed up, based on custom menus that have been set up in other mdb's?  Are the MS Access menu settings stored at the mdb level, are they stored at the application level, or are they a little bit of both.  My concern is that on of my users opens an mdb that sets up custom menus or deletes native menus, and then those changes get carrried over to my mdb.

I am concerned that if I manually set up the custom menus (as shown in jamiesoftware example), that if they ever get screwed up I'd have to go in and reset the menus manually all over again.
If I set the menus with code, this reset would be much easier.

I hope this makes sense.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Custom menus are stored in the MDB and are MDB specific.  However, changes to the default menus are not mdb specific ... and will be seen in any mdb that is open.  You should *prevent* users from making any changes to menus. Period.

Also, IF ... you have say a custom menu ... you can set that menu to be in effect when Access load ... via the AutoExec macro - which can call a Function to set the menu (and other custom settings).  When you do this, that menu will be in effect ... and users will not see any default menus ... and any changes made to them.

mx
0
 
CascadeGeneralAuthor Commented:
So based on what you just stated in your last comment, I can write a custom menu without screwing up the MS Access Application menu settings.

But my problem as I stated in my original question is that I need to REPLACE an existing defalut menu (in this case the right-click Popup Menu "Form Datasheet Row") with my custom menu.  Since I do not want to mess with the native menu, how do I load the menu via AutoExcec macro and have it show up instead of the native menu, without modifying or deleting the native menu?

As a side note, it looks like I may have to deal with this problem some day in Access 2007, unless I want to import menu settings from Access 2003.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I can write a custom menu without screwing up the MS Access Application menu settings."

Absolutely.

Besides 'menus', you can create Toolbars ... and Popups ... AKA 'Right Click Menus ... AKA Shortcut Menus.

Forms and all Form controls have a Shortcut Menu setting, wherein you specify a SC menu you have created.  And specifying a SC menu at the Control level overrides what is set at the Form level ... which is very cool.

To set a custom menu bar ...

1) Create a function similar to this in a VBA Module:

Public Function APP_AutoExec() As Boolean

    Application.MenuBar = "YourCustomMenuBarNameHere"
   
End Function

2) Create a macro with the exact name AutoExec.
Set the Action to be RunCode
Then put this for the Function Name:

APP_AutoExec()

Sadly, none of what I've said applies to A2007, since the entire paradigm has been changed to the Ribbon paradigm.  However, if you convert an A2003 mdb with custom menus to A2007 ... they WILL show up on the A2007 Ribbon ... under the 'Add-In' menu.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Not sure what else I can add.  I've built 100's of custom menus, toolbars and shortcut menus.  Easy to do, easy to change, etc.

I have to sign off now.  If you could close this Q before midnight, I would appreciate that.

btw ... here is a free app that could come in handy ... for code examples if nothing else:

http://www.lebans.com/commandbars.htm

thx.mx
0
 
CascadeGeneralAuthor Commented:
I understand your example, but could you add a little more detail as to how it would specifically replace the native menu "Forms Datasheet Row", which is the right click menu for a form/subform datasheet?  I can write a generic custom menu, but I cannot figure out how to have it appear instead of the native right-click popup menu titled "Forms Datasheet Row".
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't think you can replace a native toolbar ... as mx said, you can replace the main Access menubar, but that's not what you're after it seems.

You can alter the "Forms Datasheet Row" toolbar, and that would stay in place for ALL databases on that machine. If that's not what you're after, then you can:

1) Store the current settings of that toolbar (in a table, perhaps)
2) Make your changes in the current database, perhaps when the db opens
3) Reset that toolbar when the current database closes.

The link mx gave you first will give instructions on working with builtin toolbars, but please post back if you need further help.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Are you referring to this Right Click menu (see attached image) ?

If so, you can definitely replace that, which I've have done 100's of times ... in fact, I always replace it with a subset of the default items.

Using the technique in the article, create a Popup menu ... and then assign this custom shortcut menu to the Form's Shortcut Menu Bar on the Form's property sheet.

mx
RightClickDatasheet.gif
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Here is a custom one for example called aorSbEditFilterSort.  The 3 images show the setup after you have created the Custom short cut menu  ... aka Popup aka Right Click.

btw ... I don't see a native shortcut menu called '"Forms Datasheet Row" .  The 4th image below shows all native Form shortcut menus.

mx
CustomShortCutMenu01.gif
CustomShortCutMenu02.gif
CustomShortCutMenu03.gif
CustomShortCutMenu04.gif
0
 
CascadeGeneralAuthor Commented:
I dug up an mdb that maybe shows what I want to do.  This solutoin does exactly what I want, is extremely simple and flexible, and doesn't require screwing with the native toolbars.  I just can't figure out how to carry this logic over the the Datasheet, since it doesn't have the Event Procedure that are listed in this example.

The attached mdb contains Form1, which contains an Office Spreadsheet Control.  When you right-click on this spreadsheet, you get the native toolbar.  However, if you comment out the "Exit Sub" code in Spreadsheet Event Procedures titled "BeforeContextMenu" and "CommandExecute", you get my custom toolbar.  If you remove the comments, you get the original native toolbar for righ-clicking on the Spreadsheet.

I just want to be able to do the same thing with a datasheet (if possible).


Option Compare Database

Private Sub Spreadsheet0_BeforeContextMenu(ByVal x As Long, ByVal y As Long, _
ByVal Menu As Object, ByVal Cancel As Object)
Dim aMenu(8)

'COMMENT OUT TO RUN CUSTOM TOOLBAR
Exit Sub

aMenu(0) = Array(" U&ndo", "oUndo")
aMenu(1) = Empty
aMenu(2) = Array("Cu&t", "oCut")
aMenu(3) = Array("&Copy", "oCopy")
aMenu(4) = Array("&Paste", "oPaste")
aMenu(5) = Empty
aMenu(6) = Array("T&est", "oTest")
aMenu(7) = Empty
aMenu(8) = Array("&Help", "oHelp")
    
Menu.Value = aMenu

End Sub
Private Sub Spreadsheet0_CommandExecute(ByVal Command As Variant, ByVal Succeeded As Boolean)
Dim ssConst
 
'COMMENT OUT TO RUN CUSTOM TOOLBAR
Exit Sub
 
Set ssConst = Spreadsheet0.Constants
     
If VarType(Command) = vbString Then
    With Spreadsheet0
        Select Case Command
            Case "oUndo"
                .Commands(ssConst.ocCommandUndo).Execute
            Case "oCut"
                .Commands(ssConst.ocCommandCut).Execute
            Case "oCopy"
                .Commands(ssConst.ocCommandCopy).Execute
            Case "oPaste"
                .Commands(ssConst.ocCommandPaste).Execute
            Case "oTest"
                Call TestMsg
            Case "oHelp"
                .Commands(ssConst.ocCommandHelp).Execute
        End Select
    End With
End If

End Sub
'--------------------------------------------------------------------------------------------
Private Sub TestMsg()

MsgBox "This is a Custom Toolbar test message"

End Sub

Open in new window

Right-Click-Menu-Example-00.mdb
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
<sigh>

Well, sorry but I'm not familiar with the Office Spreadsheet Control nor do I plan to be.  The method I showed you requires no code and is simple to create ... and does not require messing with the native menus.

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<If so, you can definitely replace that>

To be clear, you cannot "replace" the builtin shortcut (i.e. "popup" menus), but you can do as mx suggests and set the Form property to show your custom menus.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, I meant 'replace' it on the form with a custom one.

scott ... are you all dialed in for the summit ?  I made plane res yesterday.

mx
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 9
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now