PDA

View Full Version : Disable Insert Worksheet Command from toolbar



Niko
05-04-2005, 05:18 AM
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
05-04-2005, 07:08 AM
Just protect the workbook to stop the user from adding worksheets.

Bob Phillips
05-04-2005, 10:08 AM
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
05-04-2005, 11:02 PM
Hi DRJ, Hi xld

Thanks a lot for the response.

The solution by xld works just perfectly.

:friends:
:beerchug:
Niko

Niko
05-05-2005, 12:04 AM
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
05-05-2005, 01:24 AM
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
05-06-2005, 07:52 AM
Thanks for the update Niko,

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

Cheers

Dave

Richie(UK)
05-07-2005, 09:17 AM
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
04-21-2010, 04:00 AM
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
04-21-2010, 04:07 PM
Application.OnKey "%+{F1}",""

chaharpragya
04-21-2010, 10:55 PM
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.