Disable "Hyperlink" menu item

[Murray]

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

 

[NickHK]

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

 

[Ron de Bruin]

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

 

[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















- 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