Disable Insert Worksheet feature

[WisdomandLaughter]

Good Day,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
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></o>
<o></o>
Help please! <o></o>
<o></o>

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></o>
<o>Thank you.</o>

 

[lenze]

Can't you just protect that WorkBook for structure?
lenze

 

[WisdomandLaughter]

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.

 

[ColinKJ]

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

 

[Tom Urtis]

ADVERTISEMENT

I am attempting to disable the Insert Worksheets feature.

http://www.mrexcel.com/forum/showthread.php?t=369313

 

[WisdomandLaughter]

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

 

[Tom Urtis]

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.