Disable File>Share in Excel with VBA

Question

I need a way to disable the ability for a user to go to (or use) the menu File > Share

I have similar for other save commands like below but need something for this feature as well

Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False

I have tried:

Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Share").Enabled = False

to no avail

My aim is to stop users saving copies of this file (Hosted on a server), I fully understand Excel isn't meant to be secure and there is always a way to do this but want to make it as hard as I can for the average Joe

Regards

What version of Excel are you using? – CLR Jan 05 '18 at 12:53 — CLR, Jan 05 '18 at 12:53
2013, but my users are a mix of 2010 and 2013 – Aurelius Jan 05 '18 at 14:25 — Aurelius, Jan 05 '18 at 14:25

score 1 · Answer 1 · answered Jan 05 '18 at 12:49

You could use a for each loop to extract the available commands:

' Iterate available commands from the file command bar.
Sub PrintAllCommandBarControlNames()

    Dim cbControl As CommandBarControl
    For Each cbControl In Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls

        Debug.Print cbControl.Caption
    Next
End Sub

Run on Excel 2010, I couldn't find a share option. Might just be my system. The above returned:

&New...
&Open...
&Close
&Save
Save &As...
Single Web Page (*.mht)
Save &Workspace...
File Searc&h...
Per&mission...
Per&mission
Ch&eck Out
Ch&eck In...
Ve&rsion History...
We&b Page Preview
Page Set&up...
Prin&t Area
Print Pre&view
&Print...
Sen&d To
Propert&ies
&Recent File Name Goes Here
&Recent File Name Goes Here
Sign ou&t
E&xit Excel

I get the same list (Excel 2013), I tried the "Send To" as it sounded promising but it wasn't that either sadlty — Aurelius, Jan 05 '18 at 14:36

score 1 · Answer 2 · answered Jan 05 '18 at 15:07

The 'backstage' menu (that you get when you click top left File menu) is effectively part of the Ribbon, and not a Command Bar. If you tried disabling Save for instance, using your example, you'll find they don't work on 2010/2013 etc.

There is this answer which tells you how to manipulate those menu items in the ribbon: Remove "Save & Send" from File menu in Excel 2010 using custom XML and one of the items is called TabShare.

Disable File>Share in Excel with VBA

2 Answers2