disable "unprotect sheet" in tools menu bar with VB

[Mary Cullen]

In my VB code I want to disable the "unprotect sheet" command in
tools/protection. I am trying to avoid password protecting the sheet

 

[Jim Thomlinson]

I am not saying don't do it but it is a whole pile easier to password protect
the sheet than it is to effectively disable the menu item. The problem is not
with disabling the menu item. It is ensuring that you reset it appropriately.
You need to determine when the book opens, is activated, deactivated, or
closed and set the item appropriately. And all of this assumes that macros
are enabled. If they are not then you can not enable/disable the item...

If you still want to do this then you need to look at the events I listed in
ThisWorkbook and add the appropriate code to those events. Also note that the
end user could have placed a protect/unportect button directly onto one of
their toolbars so you will want/need to search through all open toolbars
looking for this button.

I personally would just add a password. Note that if you want to break the
password on a sheet that can be done with very simple code in about 2
minutes...

 

[Mike H]

Seems a bit sledghammer/walnut to me but anyway, try this

Sub Disableit()
With Application.CommandBars("Worksheet Menu Bar")
With .Controls("&Tools")
With .Controls("&Protection")
.Controls("Unprotect Sheet...").Enabled = False
End With
End With
End With
End Sub

Mike

 

[Ron de Bruin]

Hi Mike

Tip: Never use the name of the controls
If your workbook is used in a non English version your code will blow up

Use the ID of the control
See
http://www.rondebruin.nl/menuid.htm

 

[Mike H]

Ron,

Thanks, I've actually read you article and it's an excellent source of
information.
http://www.rondebruin.nl/menuid.htm

I was a bit lazy and did it this way for 2 reasons, Frstly, I could do it in
my head because names are intuative whereas numbers aren't. But more
importantly on your web page the menu numbers are given for Tools|Protection
but not for the next level down i.e. protect/unprotect. Where can I find
these?

Mike

 

[Ron de Bruin]

Hi Mike

Download OLE's add-in, the link is above the screenshots that I make for the lazy people <vbg>
http://www.rondebruin.nl/menuid.htm#Screenshots

Have a nice day

 

[Mary Cullen]

Mike, I tried it and it disabled "Protection" which is fine as I don't really
need the next level but when I run it again with Enabled=True it's still
disabled.

Mary

 

[Mike H]

Mary,

You shouldn't need to go to these lengths, switching should be as simple as
changing TRUE ot FALSE and I don't understand why it didn't work. However
here's a routing to toggle it to the opposite state it is currently in. I
suggest you have a look at Ron De Bruins webpage noted in your other reponse
where he points out a pitfall in the solution I've given.

Sub Disableit()
With Application.CommandBars("Worksheet Menu Bar")
With .Controls("&Tools")
With .Controls("&Protection")
If .Controls("Unprotect Sheet...").Enabled = True Then
.Controls("Unprotect Sheet...").Enabled = False
Else
.Controls("Unprotect Sheet...").Enabled = True
End If
End With
End With
End With
End Sub

Mike

 

[Mary Cullen]

Thanks Mike, in the interim I tried the following code and it seems to work
fine. The responses I've gotten have helped and I have a better understanding
of how this works and the potential pitfalls. I appreciate the help.

Mary

 

[Mary Cullen]

Ron, great information from your menuid link, 2 questions though

1) will the ID of the control work if a user does have "Always show full
menus" checked
2) do you know if will this work in Excel 2007

Thanks, Mary

 

[Ron de Bruin]

Hi Mary

menus" checked

Yes

In 2007 there is no Tools>Protection
You need xml to disable controls

See the examples on my site for the UI editor
http://www.rondebruin.nl/ribbon.htm

 

[Mary Cullen]

Ron, thank you so much for your help. I checked out your site. I sent the
address to my husband as well as he does a lot with Excel and VB too.

Mary

 

[monir]

Jim;

Note that if you want to break the password on a sheet that can be done with
very simple code in about 2 minutes...

Just out of curiosity and concern, and please don't provide much details!! :
1) Can the same be done for password-protected VBAProject Properties
(protected macros) ??
1) Can the same be done for password-protected workbooks ??
2) Are these "simple" codes readily available to average users or only to
Excel Programming experts ??
3) I suppose once the password is broken, then the items are no longer
password protected for subsequent access. Correct ??
3) Can you actually identify the password in any case ??

Thank you.
Monir

 

[Dave Peterson]

1) Yep. It can work quicker!
2) There are commercial programs available that will break the password to
open.
3) Yep. Although, I'd save as a new name so I have both the protected version
and the unprotected versions.
4) For the workbook password and sheet password (under tools|protection), you
can identify the password--but it may not be the password that was specified.

I would think that commercial software would support all the stuff you ask.

Visit JE McGimpsey's site:
http://mcgimpsey.com/excel/removepwords.html

For lots more info.

 

[monir]

Dave;

Thank you very much for your reply.
Regards.