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

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...
--
HTH...

Jim Thomlinson


"Mary Cullen" wrote:

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

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

"Mary Cullen" wrote:

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

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mike H" <(E-Mail Removed)> wrote in message news:62CA3E7C-AFF3-42D4-88DD-(E-Mail Removed)...
> 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
>
> "Mary Cullen" wrote:
>
>> In my VB code I want to disable the "unprotect sheet" command in
>> tools/protection. I am trying to avoid password protecting the sheet

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" wrote:

> 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
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Mike H" <(E-Mail Removed)> wrote in message news:62CA3E7C-AFF3-42D4-88DD-(E-Mail Removed)...
> > 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
> >
> > "Mary Cullen" wrote:
> >
> >> In my VB code I want to disable the "unprotect sheet" command in
> >> tools/protection. I am trying to avoid password protecting the sheet
>

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mike H" <(E-Mail Removed)> wrote in message news:533D2C8E-761D-4C3E-8342-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Mike H" <(E-Mail Removed)> wrote in message news:62CA3E7C-AFF3-42D4-88DD-(E-Mail Removed)...
>> > 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
>> >
>> > "Mary Cullen" wrote:
>> >
>> >> In my VB code I want to disable the "unprotect sheet" command in
>> >> tools/protection. I am trying to avoid password protecting the sheet
>>

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" wrote:

> 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
>
> "Mary Cullen" wrote:
>
> > In my VB code I want to disable the "unprotect sheet" command in
> > tools/protection. I am trying to avoid password protecting the sheet

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" wrote:

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

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

"Mike H" wrote:

> 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" wrote:
>
> > In my VB code I want to disable the "unprotect sheet" command in
> > tools/protection. I am trying to avoid password protecting the sheet

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" wrote:

> 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
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Mike H" <(E-Mail Removed)> wrote in message news:62CA3E7C-AFF3-42D4-88DD-(E-Mail Removed)...
> > 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
> >
> > "Mary Cullen" wrote:
> >
> >> In my VB code I want to disable the "unprotect sheet" command in
> >> tools/protection. I am trying to avoid password protecting the sheet
>