Delete Insert Worksheet on Open/Reset on Close

[Guest]

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.

 

[Guest]

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.