Delete Insert Worksheet on Open/Reset on Close

[Jane]

New at VB. Need to delete Insert Worksheet from file menu at open and reset
at close. I got this code from MS site. Doesn't reset at close unless I hit
the F5 key.
Sub menuItem_Delete()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Insert")
myCmd.Controls("Worksheet").Delete
End Sub
Sub MenuBar_Restore()
CommandBars("Insert").Reset
End Sub
Any help will be appreciated much.

[Tom Ogilvy]

First, I wouldn't use Reset - what if the user has other customizations?

I think you want to make the control not visible

Private Sub workbook_Open()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Insert")
myCmd.Controls("Worksheet").Visible = False
End Sub

Private sub workbook_BeforeClose()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Insert")
myCmd.Controls("Worksheet").Visible = True
End sub

this code would need to be in the ThisWorkbook module.

For information on Events, see Chip Pearson's site

http://www.cpearson.com/excel/events.htm

all that said, it might be better just to protect the structure of your
workbook (Tools=>Protect=>Protect Workbook)

that would disallow the insertion or deletion of worksheets.

--
Regards,
Tom Ogilvy






"Jane" wrote:

> New at VB. Need to delete Insert Worksheet from file menu at open and reset
> at close. I got this code from MS site. Doesn't reset at close unless I hit
> the F5 key.
> Sub menuItem_Delete()
> Dim myCmd As Object
> Set myCmd = CommandBars("Worksheet menu bar").Controls("Insert")
> myCmd.Controls("Worksheet").Delete
> End Sub
> Sub MenuBar_Restore()
> CommandBars("Insert").Reset
> End Sub
> Any help will be appreciated much.