CODEWITHSUNDEEP
vbaexcel
Aurelius
Aurelius

Reputation: 485

Disable File>Share in Excel with VBA

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

Upvotes: 3

Views: 1041

Answers (2)

CLR
CLR

Reputation: 12279

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.

Upvotes: 1

David Rushton
David Rushton

Reputation: 5030

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

Upvotes: 1

Related Questions