View Full Version : Disable Insert Worksheet Command from toolbar
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).
Hi DRJ, Hi xld
Thanks a lot for the response.
The solution by xld works just perfectly.
:friends:
:beerchug:
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.