Consulting

Results 1 to 8 of 8

Thread: Disable Power Pivot Menu

  1. #1

    Post Disable Power Pivot Menu

    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 >
    Last edited by Aussiebear; 04-17-2023 at 11:49 AM. Reason: Added code tags to supplied code

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  3. #3
    Quote Originally Posted by Artik View Post
    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!

  4. #4
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  5. #5
    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.

  6. #6
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by GoofyMan View Post
    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

  7. #7
    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>
    ....
    Last edited by Aussiebear; 04-18-2023 at 01:52 PM. Reason: Added code tags to supplied code (Again...)

  8. #8
    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •