Enabling vba-autofilters on a protected AND shared workbook

Hello all,

I'm having a strong problem with a workbook I need to save in both shared (MultiUserEditing = True) and protected state. In such a workbook I need to run some vba macros to activate or inhibit some auto-filters...

The solution on a non-shared workbook is quite simple: simply protect the worksheet HMI with

Please Login or Register  to view this content.

and it's done... Almost done, because the UserInterfaceOnly attribute is not saved! (Don't ask me why, but this is explicitly stated in the Worksheet.Protect help). Thus, it shall be re-applied when the workbook is opened (like wks.Protect UserInterfaceOnly:=True in the Workbook_Open callback) in order to get the same protection behaviour and having my auto filters working well with my macros.
A bit complex, but that's fine however.

BUT when the workbook is protected, then shared, it's pretty different ! Indeed, (0) on a shared workbook, the protection cannot be changed. This means that when I save my workbook, (1) I loose the UserInterfaceOnly:=True attribute of the protection and when I reopen it later, (2) I cannot restore it because the workbook is shared (thus protection cannot be modified)...

I feel a bit tricked with circling problem... (0) prevents me to restore the protect UserInterfaceOnly attribute but (0) doesn't prevent that attribute to be lost...

Any advice ?
Thanks !

Hi TMS,

I know that, this is the statement "(0)" in my post.

But it's really confusing that on a protected workbook (with UserInterfaceOnly set to True) which is later shared then saved, the UserInterfaceOnly attribute of the protection that was True is turned to False automatically !!
Here even the wbk is shared, excel itself changes the protection attributes at saving .... I wonder why !? And this is my blocking point....

Alexandre