Preserve Custom Menu Command (Excel XP >)

[Rudi]

I have a workbook that contains a macro in the Workbook_Open command. The macro creates a new menu item to the right of the help menu. When the workbook closes, the new menu item is removed by a macro attached to the Workbook_Close event.

My question is: How can I prevent this new menu item from being deleted when the user resets the Worksheet Menu Bar from the Customise command. Can I prevent them from resetting the menu bar, or even better, have a macro recreate the button after the reset event occurs.

Any ideas?

[HansV]

You could protect the worksheet menu bar after adding the control in the Workbook_Open event:

Private Sub Workbook_Open()
With Application.CommandBars("Worksheet Menu Bar")
With .Controls.Add(Type:=msoControlPopup)
.Caption = "Test Item"
... ' other code goes here
.Visible = True
End With
.Protection = msoBarNoCustomize
End With
End Sub

and unprotect it before deleting the control in the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
With Application.CommandBars("Worksheet Menu Bar")
.Protection = msoBarNoProtection
.Controls("Test Item").Delete
End With
End Sub

[HansV]

Alternatively, place the control on a custom toolbar instead of on the worksheet menu bar. See star post <!post=Distributing an Excel application with toolbars (5/95/97/2000/2002),200526>Distributing an Excel application with toolbars (5/95/97/2000/2002)<!/post>.

[Rudi]

aaahh...

As simple as a protection property!!! And my mind was racing through new event procedures and macros to cancel the event of resetting...etc.

Well...you saved me a stack of time on this one ... thx

[Rudi]

Also an idea...but I prefer your first solution...as it's easier and will suffice!