Disable share workbook option in VBA

[Les Allan]

I've got a workbook with a lot of code and want to prevent users from
sharing the workbook. If they share the workbook, it only generates
error messages for them and creates problems.

If I could hide the command bar option or catch when they go to share
the workbook and cancel the action, that would be great. But I
searched high and low on how to do this with no luck. Users will be
using XL 2002 up to 2007. Does anyone have any ideas or faced this
challenge before?

Les

 

[john]

Les,
you can disable a commandbar menu item

Sub Disable_ShareWorkbook_MenuItem()
'xl2003
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=2040, Recursive:=True).Enabled = False

End Sub

can't help you with 2007 version.

 

[Les Allan]

Thanks John,

That works like a charm in XL 2003. It would be a treat if I can also
stop XL 2007 users from sharing the workbook. I thought of capturing
the user clicking on the share workbook option in the
Workbook_BeforeSave procedure with something like:

With ThisWorkbook
If .MultiUserEditing Then
<code that cancels sharing>
End If
End With

But this only triggers once the workbook is already saved. Has anyone
else tried to prevent w/b sharing in XL 2007? Or what do other people
do with users that try to share a w/b that will lead to errors if
shared?

Les