CommandBar 'Insert'

[Geoff]

Hi
In xl2003 I cannot disable the 'Insert' commandbar or any of it submenus.

This code works for other commandbars for example
Application.CommandBars("Format").Enabled = False
or
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=178, Recursive:=True).Enabled = False
or
With CommandBars("Edit")
.Controls("Delete Sheet").Enabled = False
.Controls("Move or Copy Sheet...").Enabled = False
End With

So why not this? It runs without error but doesn't do the job.
Application.CommandBars("Insert").Controls("Worksheet").Enabled = False

Grasping at straws are there reasons why commandbars cannot be disabled for
example I use
Rows("1:1").Insert Shift:=xlDown later in the code.

Appreciate any advice on this.

T.I.A.

Geoff

 

[Leith Ross]

Hello Geoff,

When specifying the control name as part of the Controls collection, it
must appear the same as on the menu. This means the shortcut key (the
underlined letter) must also be present. This is entered using the &
character before the letter that will be underlined.

You used in your code...
Application.CommandBars("Insert").Controls("Worksheet").Enabled = False

Try this version...
Application.CommandBars("Insert").Controls("&Worksheet").Enabled =
False

Note: There is no spaces in "worksheet". The post here at ExcelForum
shows them, your post may not.

Sincerely,
Leith Ross

 

[Geoff]

Hi Leith
Thanks for your reply, that works fine though I had thought the 'exactness'
referred only to including the elipses for example. This issue of mine is
mystifying because some time after posting, both my statements began to work
as expected viz to disable Worksheet in Insert:
Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=852,
_Recursive:=True).Enabled = False
Application.CommandBars("Insert").Controls("Worksheet").Enabled = False
(without the ampersand)

In explanation of my steps rather than definitive reasoning all I did was
reset the WorkSheet menu Bar using :

Application.CommandBars.ActiveMenuBar.Reset
whereas my normal practice would be to delete the custom menu with:

Application.CommandBars.ActiveMenuBar.Controls("Custom Tools").Delete
I then saved the wbook on closing and to be absolutely sure I did a reboot <g>

My reluctant conclusions therefore have to be:
i had in the past experimented or something with Insert and left it
non-standard or
an add-in had done the same

Finally, if it is necessary to use all characters in the submenu name does
this mean because my statements work without the ampersand that still my
WorkSheet Menu is not back to standard?

This will 'niggle' me now but at least all 3 statements appear to work as
expected and again I thank you for your thoughts which were effective.

Geoff