Hi all,
I'm using Excel 2007, and I want to prevent users from un-ticking the shared workbook box. My searches so far come up with 2 solutions, which do not work for me:
1) Use "Protect & Share workbook" with a password. But this is unsuitable because it appears you can only protect the sharing with a password <u>if you agree to track changes</u>. But I do not want to track changes. The workbook is shared among several users, and changes are extremely frequent: if I were to track them, then the change history would become enormous and the file would become unnecessarily bloated, slow and unstable. I can not work out why Microsoft decided to link the sharing-with-protection feature ONLY to those workbooks with changes being tracked.
2) Use the following code:
Application.CommandBars("Worksheet menu bar").Controls("Tools").Controls("Share Work&book...").Enabled = False
Apparently this is supposed to grey-out the menu item, so while it does not actually prevent sharing being turned off, it stops casual users from being able to do it through the UI. However I have tried this, and it does nothing at all. The forum posts which contain this advice refer to Excel 2003: so it is likely that the ribbon structure in Excel 2007 is labelled differently. However, I can not find any similar code which should work for Excel 2007.
Can anyone help please? All I want is to stop casual users from being able to un-share the workbook, without having to track changes.
Thanks.
I'm using Excel 2007, and I want to prevent users from un-ticking the shared workbook box. My searches so far come up with 2 solutions, which do not work for me:
1) Use "Protect & Share workbook" with a password. But this is unsuitable because it appears you can only protect the sharing with a password <u>if you agree to track changes</u>. But I do not want to track changes. The workbook is shared among several users, and changes are extremely frequent: if I were to track them, then the change history would become enormous and the file would become unnecessarily bloated, slow and unstable. I can not work out why Microsoft decided to link the sharing-with-protection feature ONLY to those workbooks with changes being tracked.
2) Use the following code:
Application.CommandBars("Worksheet menu bar").Controls("Tools").Controls("Share Work&book...").Enabled = False
Apparently this is supposed to grey-out the menu item, so while it does not actually prevent sharing being turned off, it stops casual users from being able to do it through the UI. However I have tried this, and it does nothing at all. The forum posts which contain this advice refer to Excel 2003: so it is likely that the ribbon structure in Excel 2007 is labelled differently. However, I can not find any similar code which should work for Excel 2007.
Can anyone help please? All I want is to stop casual users from being able to un-share the workbook, without having to track changes.
Thanks.
