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.

Your previous posting of this question was answered.

--
Regards,
Tom Ogilvy


"Jane" wrote:

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.

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").Del ete
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...
--
HTH...

Jim Thomlinson


"Jane" wrote:

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.

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

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").Del ete
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...
--
HTH...

Jim Thomlinson


"Jane" wrote:

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.

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 EditMove or CopyCreate 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).

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

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").Del ete
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...
--
HTH...

Jim Thomlinson


"Jane" wrote:

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.

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.

--
Regards,
Tom Ogilvy


"Jane" wrote:

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 EditMove or CopyCreate 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).

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

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").Del ete
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...
--
HTH...

Jim Thomlinson


"Jane" wrote:

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.

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").Vis ible = False
CommandBars("Ply").Controls("Insert...").Visible = False
End Sub

Sub MenuBar_Restore()
CommandBars("Worksheet menu
bar").Controls("Insert").Controls("Worksheet").Vis ible = 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 :-)
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

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").Del ete
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...
--
HTH...

Jim Thomlinson


"Jane" wrote:

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.