Can I PREVENT a user from protecting a sheet

[hairyfingers]

Is it possible via VBA to prevent a user from protecting a sheet?

Why would I want this?

I have a large spreadsheet that is locked down in every way with password protection at the sheet & workbook level - apart from 3 sheets that the users can do what they want with - they are unprotected.

The problem is if a user chooses to protect any of those sheets, and chooses a password that isn't the same as my password, the macro falls over trying to unprotect the user sheet.

I could use error resume next whilst trying to unprotect those sheets
I could skip over the usersheets when trying to unprotect them

but what I'd like to do is have something in the sheet itself preventing it in the first place


possible?

 

[Akuini]

It can be done but I'm not sure in what event to put the code.
I suggest in 2 event, i.e:
“Private Sub Workbook_SheetDeactivate” : if user protect one of those sheets and then move focus to another sheet then this will force the focus back to the sheet.
“Private Sub Workbook_BeforeSave” : if user protect one of those sheets then the code will prevent the user to save the workbook.

Put the sheet’s names that must not be protected in this array, change the sheet's name to suit in this line (in both subs):
Array("sheet1", "sheet2", "sheet3")

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Workbook_BeforeSave([COLOR=Royalblue]ByVal[/COLOR] SaveAsUI [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR], Cancel [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR])
[COLOR=Royalblue]Dim[/COLOR] z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR], ws [COLOR=Royalblue]As[/COLOR] Worksheet

        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] ws [COLOR=Royalblue]In[/COLOR] ActiveWorkbook.Worksheets
            [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] z [COLOR=Royalblue]In[/COLOR] Array([COLOR=brown]"sheet1"[/COLOR], [COLOR=brown]"sheet2"[/COLOR], [COLOR=brown]"sheet3"[/COLOR])
                [COLOR=Royalblue]If[/COLOR] UCase(ws.Name) = UCase(z) [COLOR=Royalblue]And[/COLOR] ws.ProtectContents [COLOR=Royalblue]Then[/COLOR]
                        MsgBox [COLOR=brown]"You can't protect sheet "[/COLOR] & ws.Name
                        Cancel = [COLOR=Royalblue]True[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Workbook_SheetDeactivate([COLOR=Royalblue]ByVal[/COLOR] Sh [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR])
[COLOR=Royalblue]Dim[/COLOR] z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR], ws [COLOR=Royalblue]As[/COLOR] Worksheet

        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] ws [COLOR=Royalblue]In[/COLOR] ActiveWorkbook.Worksheets
            [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] z [COLOR=Royalblue]In[/COLOR] Array([COLOR=brown]"sheet1"[/COLOR], [COLOR=brown]"sheet2"[/COLOR], [COLOR=brown]"sheet3"[/COLOR])
                [COLOR=Royalblue]If[/COLOR] UCase(ws.Name) = UCase(z) [COLOR=Royalblue]And[/COLOR] ws.ProtectContents [COLOR=Royalblue]Then[/COLOR]
                MsgBox [COLOR=brown]"You can't protect sheet "[/COLOR] & ws.Name
                ws.Activate
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

 

[Logit]

.
My 2 cents ....

I believe you will need to use XML Markup Language which allows you to create new or edit existing menu items. There are plenty of websites that describe and teach
XML.

Another consideration would be to completely hide the menu bar from the user when they go to the sheet in question. When they leave that sheet, you can unhide the menu
bar.

 

[Jaafar Tribak]

If you don't mind using an API timer, I think I could write some small code retrieve the entered password and unprotect the worksheet as soon as the sheet is protected.