Greetings

The following piece of code works fine:

CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save
&As...").Enabled = False

However, if I change it to try and disable the Hyperlink menu item, it
will not work:

CommandBars("Worksheet Menu
Bar").Controls("Insert").Controls("Hyperl&ink...).Enabled = False

I then attempted to do it by brute force with equal lack of success:

For Each m1 In Application.CommandBars(1).Controls
For Each m2 In m1.Controls
If m2.Caption = "Hyperl&ink..." Then
m2.Enabled = False
End If
Next m2
Next m1

Can anyone please explain to me why these methods fail to disable the
Hyperlink menu item, and if there is a way to do it?

Thanks and regards

Murray

Murray,
With the addition of the missing " at the end of "Hyperl&ink...", it worked
from the Immediate window:

CommandBars("Worksheet Menu
Bar").Controls("Insert").Controls("Hyperl&ink...").Enabled = False
There is also:
CommandBars("Cell").Controls("Hyperl&ink...").Enabled = False
which appears on the right-click menu of a cell.

NickHK

"Murray" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Greetings
>
> The following piece of code works fine:
>
> CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save
> &As...").Enabled = False
>
> However, if I change it to try and disable the Hyperlink menu item, it
> will not work:
>
> CommandBars("Worksheet Menu
> Bar").Controls("Insert").Controls("Hyperl&ink...).Enabled = False
>
> I then attempted to do it by brute force with equal lack of success:
>
> For Each m1 In Application.CommandBars(1).Controls
> For Each m2 In m1.Controls
> If m2.Caption = "Hyperl&ink..." Then
> m2.Enabled = False
> End If
> Next m2
> Next m1
>
> Can anyone please explain to me why these methods fail to disable the
> Hyperlink menu item, and if there is a way to do it?
>
> Thanks and regards
>
> Murray
>

Hi

Or use the ID, also working in non English Excel versions then

Sub MenuControl_False()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=1567)
Ctrl.Enabled = False
Next Ctrl
End Sub

Sub MenuControl_True()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=1576)
Ctrl.Enabled = True
Next Ctrl
End Sub


See also
http://www.rondebruin.nl/menuid.htm



--

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


"NickHK" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Murray,
> With the addition of the missing " at the end of "Hyperl&ink...", it worked
> from the Immediate window:
>
> CommandBars("Worksheet Menu
> Bar").Controls("Insert").Controls("Hyperl&ink...").Enabled = False
> There is also:
> CommandBars("Cell").Controls("Hyperl&ink...").Enabled = False
> which appears on the right-click menu of a cell.
>
> NickHK
>
> "Murray" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Greetings
>>
>> The following piece of code works fine:
>>
>> CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save
>> &As...").Enabled = False
>>
>> However, if I change it to try and disable the Hyperlink menu item, it
>> will not work:
>>
>> CommandBars("Worksheet Menu
>> Bar").Controls("Insert").Controls("Hyperl&ink...).Enabled = False
>>
>> I then attempted to do it by brute force with equal lack of success:
>>
>> For Each m1 In Application.CommandBars(1).Controls
>> For Each m2 In m1.Controls
>> If m2.Caption = "Hyperl&ink..." Then
>> m2.Enabled = False
>> End If
>> Next m2
>> Next m1
>>
>> Can anyone please explain to me why these methods fail to disable the
>> Hyperlink menu item, and if there is a way to do it?
>>
>> Thanks and regards
>>
>> Murray
>>
>
>

On Feb 28, 4:52 pm, "NickHK" <TungChe...@Invalid.com> wrote:
> Murray,
> With the addition of the missing " at the end of "Hyperl&ink...", it worked
> from the Immediate window:
>
> CommandBars("Worksheet Menu
> Bar").Controls("Insert").Controls("Hyperl&ink...").Enabled = False
> There is also:
> CommandBars("Cell").Controls("Hyperl&ink...").Enabled = False
> which appears on the right-click menu of a cell.
>
> NickHK
>
> "Murray" <myall_bl...@hotmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > Greetings
>
> > The following piece of code works fine:
>
> > CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save
> > &As...").Enabled = False
>
> > However, if I change it to try and disable the Hyperlink menu item, it
> > will not work:
>
> > CommandBars("Worksheet Menu
> > Bar").Controls("Insert").Controls("Hyperl&ink...).Enabled = False
>
> > I then attempted to do it by brute force with equal lack of success:
>
> > For Each m1 In Application.CommandBars(1).Controls
> > For Each m2 In m1.Controls
> > If m2.Caption = "Hyperl&ink..." Then
> > m2.Enabled = False
> > End If
> > Next m2
> > Next m1
>
> > Can anyone please explain to me why these methods fail to disable the
> > Hyperlink menu item, and if there is a way to do it?
>
> > Thanks and regards
>
> > Murray- Hide quoted text -
>
> - Show quoted text -

Doh....

I can't believe I mucked around with this for about two hours
yesterday for something so simple.

Thanks NickHK for your help. Ron - thanks also for your reply and
suggestion.

Regards

Murray