Disable Power Pivot Menu

[GoofyMan]

This is awesome.

Any idea how I can disable the "Power Pivot" item menu on top of the ribbon?

I can't find the idMso of the "Power Pivot".

Looks like "Power Pivot" is not part of the "Worksheet menu bar".

Thanks!

 <tabs >
<tab 
idMso="TabHome"
visible="false"/>
<tab 
idMso="TabData"
visible="false"/>
</tabs >

[Artik]

The PowerPivot tab is not a built-in tab. It is "shipped" with the Microsoft Office Power Pivot add-in. If you disable the add-in, the tab disappears too.

Artik

[GoofyMan]

The PowerPivot tab is not a built-in tab. It is "shipped" with the Microsoft Office Power Pivot add-in. If you disable the add-in, the tab disappears too.

Artik

Thanks Artik!

You are right, i looked for it and found out that it is part of the "Application.COMAddIns" collection and called "Microsoft Power Pivot for Excel".

I spent couple of hours googling and researching, but I had no success of finding a way to do it - is it possible at all to disable the add-in in the XML? the same way we can disable a home or data tab with its idMSO?

Thanks!

[Artik]

Interference with the add-in's ribbon is not possible (or at least immoral ). As I mentioned earlier, you can disable the add-in.
This macro lists all COM add-ins installed in Excel and indicates whether the add-in is currently enabled. When working with programmatic enable/disable COM add-ins, use the progID property, not Description, although the latter is more human-friendly (but is prone to language conversions).

Sub OverviewCOMAddIns()
    Dim addIn       As COMAddIn
    
    'Results in the Immediate window
    For Each addIn In Application.COMAddIns
        Debug.Print addIn.progID, addIn.Connect, Chr(34) & addIn.Description & Chr(34)
    Next addIn

    Debug.Print String(35, "=")

End Sub

To enable/disable an add-on, you can use the following function, which returns True when the action succeeded.

Sub Test()
    If SwitchCOMAddIn("PowerPivotExcelClientAddIn.NativeEntry.1", True) Then
        'is OK
        Stop
    Else
        'Something went wrong
        Stop
    End If
End Sub


Function SwitchCOMAddIn(strprogID As String, blnConnect As Boolean) As Boolean
    Dim addIn       As COMAddIn

    On Error Resume Next
    Set addIn = Application.COMAddIns(strprogID)
    On Error GoTo 0

    If Not addIn Is Nothing Then
        If (Not addIn.Connect) = blnConnect Then
            addIn.Connect = blnConnect
        End If

        SwitchCOMAddIn = (addIn.Connect = blnConnect)
    Else
        SwitchCOMAddIn = False
    End If

End Function

Artik

[GoofyMan]

This is great Artik. I appreciate it.

As for disabling the Add-In - I thought about it. but I think it is application level, no document level.

The goal of all this story - I created a spreadsheet with plenty of pivot tables and measures (DAX measures). I sent it to tens of users. I want to prevent them form going into: power pivot --> calculate--> manage measures (they are not allowed to see the calculations).

If I disable the add-in via VBA, i would practically change their's Excel behavior for all other documents.

If there is any other way for protecting the measures I would be happy to hear. I tried to the "Protect" in the "review" tab, and this doesn't hide the measures.

[Artik]

As for disabling the Add-In - I thought about it. but I think it is application level, no document level.

Yes, disabling the add-in is at the application level, not the particular workbook.
However, you can use your workbook's events to perform certain actions at the application level. For example, you can use the Workbook_Activate event and in it, remember in a global variable whether the add-in was enabled, and then disable the add-in. And in the Workbook_Deactivate event, enable the add-in, provided that it was enabled when the workbook was activated (check the value of the global variable).

Artik

[GoofyMan]

I tried it before the ribbonX path.

It worked well but has an unintended consequence - the disabling of the ribbon in the activate and deactivate method also cleared the clipboard.

So lets say that you send an Excel file "Book1" to users. this Book1 file has macros in the activate and deactivate method:

""Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"/(""Ribbon"",True)"

The user opens book1 and book2. When they move between them Book1 doesn't have a ribbon, book2 does have.

However, when they want to copy data from book1 to book2: they select the data in book1 and crtl-c, then they move to book2 and try to paste, the "paste" is grayed out.

after long research I found out that the deactivate procedure in book1 (that was returning the ribbon when moving to book2) actually has some built in effect that clears the clipboard.

also - I am not thrilled about this path (VBA activate/deactivate) because I am afraid if the user laptop suddenly crashes, out of battery, windows failure.... the user Excel will remember the "no ribbon" state and affect their excel behavior.


I was thinking about it and maybe for my solution this workaround will do it. I tried and it did what I want but I am not sure yet if there are any side effects: clear the entire ribbon, and then rebuilt their menu items. It will only affect this one document.

<ribbon startFromScratch="true">
 <tabs>
 <tab idMso="TabHome" visible="true"></tab>
 <tab idMso="TabInsert" visible="true"></tab>
 <tab idMso="TabPageLayoutExcel" visible="true"></tab>
....

[tonyadams]

Hi Goofy,
You need to use the correct idMso for the "Power Pivot" tab. Once you've identified the idMso for the "Power Pivot" tab, you can use it to control the visibility in your customization XML. build now gg

<tabs>    <tab idMso="TabHome" visible="false"/>
    <tab idMso="TabData" visible="false"/>
    <tab idMso="TabPowerPivot" visible="false"/>
</tabs>