How to show custom shortcut menu for PivotCharts but no where else?

[munkee]

Hi all,

I have been playing with pivotcharts lately to display my data from my database. I have since found however that I will need to get rid of a lot of the "options" a user has to mess around with when I distribute out my database.

One of the easiest ways to do this has been to disable all menus and shortcut menus in my database using the startup properties. However I lose functionality in my pivot charts, namely the drill down / drill out / expand / collapse in the default shortcut menu.

I have found I can create my own and I have added the above functionality to my custom shortcut menu.

Now my issue is I have tried adding this menu to the forms properties but it still does not show.

Is this because of my startup options being incorrect?

How do I go about disabling all shortcuts/menus in my database but then allowing one to open for my pivot charts? Would creating a blank shortcut menu for all other forms do the trick and how would I go about enabling my other menu when I am on certain forms.

[munkee]

Ok I have partially got this working (I created a custom shortcut menu) but I have a new issue.

When I open my subform up directly (which is set to pivotchart view) My menu works fine.

However when I open up my main form which displays this pivotchart within a subform my menu doesnt work... any ideas?

I have attached some images to show.

[munkee]

I have done some more messing by creating a toolbar menu as well to confirm the action isnt just a issue with the shortcut menu I made when I right click.

What happens is when I click into the subforms pivot chart and select a series or a text name on the x-axis that would normally allow a drilldown I get no options in my menus, they are all greyed out.

However if I allow my right click to popup the default menu and then do the same action I get the options popup on the default menu, without them being greyed out. I also see that my custom toolbar is now showing the options being available too.

In my limited experience this is clearly showing some form of focus issue with the selection. I need to somehow work out what the default menu is doing differently before opening compared to my custom menu...

any ideas on this?

[munkee]

OK I ditched the idea of my own custom menu as there was far to much headache in trying to figure out what was causing the focus issues.

Instead I did a lot of research into disabling menu controls. Long story short I have some very disgustingly ugly code.. that works.

Option Explicit
Option Compare Database

Private Sub Form_Close()
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Dim mykilling As CommandBarControls
Dim byechart As CommandBarControls
Dim byefilter As CommandBarControls
Dim byecalc As CommandBarControls

    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, Id:=501)    'field list
        
    Set mykilling = CommandBars.FindControls(msoControlButton, 222) ' properties
    Set byechart = CommandBars.FindControls(msoControlButton, 6450) 'chart
      Set byefilter = CommandBars.FindControls(msoControlButton, 5884) 'filter
 Set byecalc = CommandBars.FindControls(msoControlButtonPopup, 6700) 'calc
    
    
    For Each ctl In myControls
        ctl.Visible = True
        
    Next ctl
    
    For Each ctl In mykilling
        ctl.Visible = True
        
    Next ctl
    
    For Each ctl In byechart
        ctl.Visible = True
        
    Next ctl
    
  For Each ctl In byefilter
        ctl.Visible = True
        
    Next ctl
    
    For Each ctl In byecalc
    ctl.Visible = True
    Next ctl
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Dim mykilling As CommandBarControls
Dim byechart As CommandBarControls
Dim byefilter As CommandBarControls
Dim byecalc As CommandBarControls

    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, Id:=501)    'field list
        
    Set mykilling = CommandBars.FindControls(msoControlButton, 222) ' properties
    Set byechart = CommandBars.FindControls(msoControlButton, 6450) 'chart
      Set byefilter = CommandBars.FindControls(msoControlButton, 5884) 'filter
 Set byecalc = CommandBars.FindControls(msoControlButtonPopup, 6700) 'calc
    
    
    For Each ctl In myControls
        ctl.Visible = False
        
    Next ctl
    
    For Each ctl In mykilling
        ctl.Visible = False
        
    Next ctl
    
    For Each ctl In byechart
        ctl.Visible = False
        
    Next ctl
    
  For Each ctl In byefilter
        ctl.Visible = False
        
    Next ctl
    
    For Each ctl In byecalc
    ctl.Visible = False
    Next ctl
End Sub

No error handling as of yet.. but I expect it to suck.