Intercepting a function in the Excel365 ribbon or disable the function using VBA

Question

I am seeking a method to disable the menu function highlighted in red in the attached image for users.

menu function

I attempted to achieve this with the following code, but I am unsure of the correct ID:

Code1

Dim Ctrl As CommandBarPopup
Set Ctrl = Application.CommandBars.FindControl(ID:=XXXX) 
If Ctrl Is Nothing Then Else Ctrl.Enabled = False

Code2

CommandBars(1).Controls("SheetView").Visible = False

Furthermore, I tried to identify the IDs using the following code and a tool named "BuiltInControlsScanner," but unfortunately, I had no success in locating the IDs or function names. They do not seem to appear, and I am uncertain why.

Dim i As Integer
Dim ii As Integer
Dim iii As Integer
Dim iiii As Integer
Dim x As Integer
Dim CB As CommandBar
Dim Menu As CommandBarControl
Dim SubMenu As CommandBarControl
Dim SubMenu2 As CommandBarControl

On Error Resume Next

x = 1

For Each CB In CommandBars
    i = 1
    ii = 1
    iii = 1
    iiii = 1
    For Each Menu In CB.Controls
        ii = ii + 1
        iii = 1
        iiii = 1
        For Each SubMenu In Menu.Controls
            iii = iii + 1
            iiii = 1
            For Each SubMenu2 In sebmenu.Controls
                Cells(x, 8) = CB.Name
                Cells(x, 9) = Menu.Caption
                Cells(x, 10) = SubMenu.Caption
                Cells(x, 11) = SubMenu2.Caption
                Cells(x, 12) = i
                Cells(x, 13) = ii
                Cells(x, 14) = iii
                Cells(x, 15) = iiii
                x = x + 1
                iiii = iiii + 1
            Next SubMenu2
        Next SubMenu
    Next Menu
Next CB

Alternatively, if the view is switched, I would like to intercept the switch and transition to a specifically tailored view for the user. I've read that it might be possible by editing the XML file, but I am unsure how to proceed, as the instructions I found were geared towards creating a custom button in the ribbon rather than editing a preset.

The objective is to prevent users in the Excel (365) file from having the ability to switch between "sheets views." Unfortunately, despite several days of searching and reading, I haven't found a solution or a viable approach.

Essentially, I need a kind of "Change-Event" triggered when the view is switched, with the triggering event being the red-highlighted function in the image. Alternatively, the optimal solution would be either to disable the function entirely or make the view switch impossible.

I hope someone here can assist me in finding a solution to this issue.

Thank you in advance for your help.

Answer 1

You can find the built-in controls IDs by using the Settings window in Excel, just find the required control in the list and hover over the mouse to get the popup window appeared like shown on the picture:

Sounds like you are trying to make changes to a custom UI from another solution. There is no way to customize a custom UI in Office applications which comes from another add-in. Instead, you need to contact the add-in developer for any public API which can be used in your code. For example, see Walkthrough: Call code in a VSTO Add-in from VBA for more information.

FYI Command bars were deprecated and should not be used any longer. The Fluent UI (aka Ribbon UI) must be used instead. Command bars were left for compatibility purposes like running built-in controls programmatically.