Disable Insert Worksheet Command from toolbar

[Niko]

Hi!,



I have developed an application in Excel using VB Editor. I expect that user should not insert any worksheet into this application.

I used following line in my VBA code

Application.CommandBars("Insert").Controls(4).Enabled = False

However, it dose not work.

The same command works for other toolbars like "File", "Format", "Tools" etc.

Can someone help me to resolve this problem?
Thanks,
Niko :dunno

P.S. I am using Excel-97.

[Jacob Hilderbrand]

Just protect the workbook to stop the user from adding worksheets.

[Bob Phillips]

Hi!,
Application.CommandBars("Insert").Controls(4).Enabled = False

There is no Insert commandbar, it is a control off the Worksheet Menu Bar.

Try this

Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = False

You may need to modify if your local language version has different named controls (the commandbar should be the same).

[Niko]

Hi DRJ, Hi xld

Thanks a lot for the response.

The solution by xld works just perfectly.

:friends:
:beerchug:
Niko

[Niko]

Hello All,

I would like to share that though the 'Workheet' option was disabled in 'Insert' menu, I was able to still insert a worksheet by right clicking in sheet tabs.

This was also resolved by using following command in VBE

Application.CommandBars("Ply").FindControl(, 945).Enabled = False

I got this suggestion from anothe simmilar forum.
refer following link
http://www.ozgrid.com/forum/showthread.php?p=167465#post167465

Thanks to all of you.

Niko:thumb

[Bob Phillips]

Hello All,
I was able to still insert a worksheet by right clicking in sheet tabs.

This was also resolved by using following command in VBE

Application.CommandBars("Ply").FindControl(, 945).Enabled = False


Same principle as the sollution offered to Niko, just a different commandbar. Niko should also add that code to his application.

[brettdj]

Thanks for the update Niko,

Using FindControl is a more robust approach than a hardcoded control reference

Cheers

Dave

[Richie(UK)]

Hi Niko,

Personally, I'd go with Jake's suggestion and protect the workbook structure.

Your end-users may not appreciate your attempts to modify their working environment. What if they have other workbooks open that they wish to modify by inserting a worksheet? What if there is a crash and the menus are not restored to their 'normal' status?

And from your perspective, what if they open the workbook without macros enabled?

Just my 2p ;)

[chaharpragya]

Hey Niko but i can add a new worksheet by keyboard shortcut using Alt+Shift+F1

can ny one help me to disable this......


[quote=Niko]Hi!,

hey Niko by the following formula it worked prfectly thanks....

Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = False

:friends:

[Bob Phillips]

Application.OnKey "%+{F1}",""

[chaharpragya]

Application.OnKey "%+{F1}",""


Hey thanks for the solution
now i have another gud question can we restrict user not to add any column in the worksheet but rows can be added but i dont want to use freeze method.