How to customize or remove the BPC/EPM Menu

[ChristianSass]

hi,

I would like to create a very customized Version of an Input form in Excel.

Is there any Chance to customize the EPM Menu when I do a right-click on a cell?

[former_member186338 · Accepted solution]

Hi Christian,

In VBA I managed to disable (not delete!) EPM Menu:

Sub DisableEPMMenu()

	Dim ContextMenu As CommandBar
	Dim ctrl As CommandBarControl

	' Set ContextMenu to the Cell context menu.
	Set ContextMenu = Application.CommandBars("Cell")

	' Disable the custom control with the Caption: EPM
	For Each ctrl In ContextMenu.Controls
	If ctrl.Caption = "EPM" Then
	ctrl.Enabled = False
	End If
	Next ctrl

End Sub

Vadim

[ChristianSass · Accepted solution]

Hi Vadim,

unfortunately this doesn't work in my case.

I trigger the macro after

"Private Sub Workbook_SheetBeforeRightClick(....)

Where do you do this?

I've added "debug.print ctrl.caption" to the coding to see what is going on during the loop but "EPM" doesn't appear

Thx

[former_member186338 · Accepted solution]

Hi Christian,

First of all simply launch my DisableEPMMenu directly (using Macros...) when you are connected to BPC.

You will have:

Before:

After:

Look on EPM!

Vadim

[former_member186338 · Accepted solution]

Then I put the function call here:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

DisableEPMMenu

End Sub

Also working fine - EPM is disabled!

Vadim

[ChristianSass · Accepted solution]

Hi,

I've created a new workbook and I've used your macro but EPM is still visible and selectable

As I said, I'ver added a line "debug.print ctrl.caption" to see what is going on the loop but EPM is not showing up

[former_member186338 · Accepted solution]

May be it's due to the language version of Excel!

Please test the code like:

Do you have EPM in the Immediate window?

Vadim

[former_member186338 · Accepted solution]

P.S. You can also loop CommandBars:

For Each ContextMenu In Application.CommandBars

        Debug.Print ContextMenu.Name

Next

to find "Cell"

[former_member186338 · Accepted solution]

And by the way - what EPM version you are using??? 2.1???

[ChristianSass · Accepted solution]

Yes, 2.1

[ChristianSass · Accepted solution]

I do have "CELL" in my direct window

[ChristianSass · Accepted solution]

That's what I tried to do but I don't have EPM

Is their a Change to Change the language Version?

[former_member186338 · Accepted solution]

It's a joke???

Current is SP23... immediate upgrade required!

It's absolutely useless to talk about possible issues of EPM SP2.1 - nobody use it...

Vadim

[gregor_dieckmann · Accepted solution]

Hi,

just for clarification, this seems to be about the EPM Add On shipped with Analysis Office 2.1. So the EPM version contained in Analysis Office corresponds to something like 'old' SP23+.

Might be that the mechanism to control the context menu of a cell has changed and it cannot be controled by pure Excel means. In the docmentation I found nothing about the context menu; there are APIs to control the ribbon.

Regards,

Gregor

[ChristianSass · Accepted solution]

I had an enlightening moment ....

I've used EPM 2.1 which is part of Analysis 2.1

Now I use SP 23 Patch 1 and your coding works perfectly

Many thanks!

Let me know when you are in Germany next time. You deserve some drinks from me