Reset VB Code Not Working on Close

[Guest]

New to VB. Want to remove the Insert Worksheet option on open and Reset at
close. Using this code:

Module 1:
Sub menuItem_Delete()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Insert")
myCmd.Controls("Worksheet").Delete
End Sub

Module 2:
Sub MenuBar_Restore()
CommandBars("Insert").Reset
End Sub

Can't get Module 2 to work on close. Any help is greatly appreciated.

 

[Guest]

Your previous posting of this question was answered.

 

[Guest]

Give this a try. It also removes the insert from the right click menu of the
tabs...

Sub menuItem_Delete()
CommandBars("Worksheet menu
bar").Controls("Insert").Controls("Worksheet").Delete
CommandBars("Ply").Controls("Insert...").Delete
End Sub

Sub MenuBar_Restore()
CommandBars("Worksheet menu bar").Reset
CommandBars("Ply").Reset
End Sub

That being said your users could still insert a sheet using Shift + F11...

 

[Guest]

Just a thought, but I suspect they would also be pretty upset that you
destroyed their menu customizations by using the ill advised RESET approach.

 

[Guest]

Very good point. I tried your suggestion and get the following error message:
Procedure declaration does not match description of event or procedure
having the same name.

I want the users to add worksheets using the Edit>Move or Copy>Create copy
so the file properties and formatting of the first worksheet continues to all
subsequent worksheets (in some cases there will be hundreds of worksheets).

 

[Guest]

I considered that but I figured if Jane wants a reset then who am I to argue.
I should have included a "careful what you wish for" section in my response,
or done what you did...

Sub menuItem_Delete()
CommandBars("Worksheet menu
bar").Controls("Insert").Controls("Worksheet").Visible = False
CommandBars("Ply").Controls("Insert...").Visible = False
End Sub

Sub MenuBar_Restore()
CommandBars("Worksheet menu
bar").Controls("Insert").Controls("Worksheet").Visible = True
CommandBars("Ply").Controls("Insert...").Visible = True
End Sub

I should be a little more careful with my replies especially on a monday
morning before I have finished my first cup of coffee

 

[Guest]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub
is the actual declaration. You should always get the declaration by using
the dropdowns at the top of the module to make sure you are a) in the right
module, b) use the correct declaration.

I wasn't providing a turn key approach, just a recommendation on what events
to use by using some pseudo code.