Disable Insert Worksheet feature

WisdomandLaughter

New Member
Joined
May 28, 2008
Messages
35
Good Day,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I am attempting to disable the Insert Worksheets feature. I found the following code and was using it with workbook open. It works great, except it is disabling insert worksheet in EVERY excel file I open, not just the file that houses the code.<o:p></o:p>
<o:p></o:p>
Help please! <o:p></o:p>
<o:p></o:p>

Code:
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = False<o:p></o:p>
Application.CommandBars("Ply").FindControl(, 945).Enabled = False<o:p></o:p>
<o:p></o:p>
<o:p>Thank you.</o:p>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
sigh....I wish it were that simple, but no I can't. There are a number of other things happening in the Workbook that require it to be unprotected. The workbook must be shared. And unless I am mistaken, if I share it then I can't include code to unprotect, perform a task, and protect again.
 
Upvote 0
Hi WisdomandLaughter,

In adition to disabling in your code,

You might want to try something like

Code:

Private Sub Workbook_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = True
End Sub

Code:

So when you move away from that workbook it is Enabled, and when you activate the workbook again, it is Disabled.

Might work

ColinKJ
 
Upvote 0
Tom and Colin,

Thank you for your responses and help. Colin, your suggestion worked perfectly.

Tom - your suggestion was what I was using originally. It worked great until I shared the workbook at which point I kept receiving an error. After much investigating I came to the assumption that it was failing because one cannot delete a worksheet in a shared workbook. I see your original advice was in response to someone who was also using a shared workbook. Out of curiosity, should the code you proposed work in a shared workbook?

Judy
 
Upvote 0
The code should work but it's hard to answer your question because I don't know what "I kept receiving an error" means that you wrote. Was it a run time error, a blue screen, really no error just a Read Only message if someone else was using the workbook at the same time...can't say unless I know more about your situation. But seeing that you have what you need from others' suggestions, so much the better.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top