Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Preserve Custom Menu Command (Excel XP >)

    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?
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Preserve Custom Menu Command (Excel XP >)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Preserve Custom Menu Command (Excel XP >)

    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>.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Preserve Custom Menu Command (Excel XP >)

    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
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Preserve Custom Menu Command (Excel XP >)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •