Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Reloading Custom Menus

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a sheet which uses two custom menus that contain numerous macro buttons. In order for these macros to stay associated with the sheet after doing a 'Save As', it is necessary to delete these toolbars on deactivating the sheet, and not merely disable them. The menus are attached to the sheet so are loaded each time it is opened.

    My problem is this:
    If I switch between workbooks, my menus are deleted as I do not want them available in other environments, but when I switch back to my original sheet without closing and reopening, my toolbars are not reloaded.

    I'm currently using the enable string to try to reactivate them but that isn't working. Is it possible to reload a deleted toolbar from within the active sheet without closing, or am I going to have to rethink my strategy?

    If I delete the toolbars using the beforeclose field, if I hit close and then cancel out to return to my sheet, the toolbars are already deleted and render the sheet unusable.

    The toolbar has been built using the custom toolbar builder, not coding, otherwise I would be able to rebuild the custom menu each time the sheet was activated.

    Any way of doing this without having to churn out masses of code to build my menus?

    [ This Message was edited by: Mr Nick on 2002-04-11 02:58 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For anybody else who has faced this problem, I found this solution at j-walk.com

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.Saved Then
    Msg = "Do you want to save the changes you made to "
    Msg = Msg & Me.Name & "?"
    Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
    Select Case Ans
    Case vbYes
    Me.Save
    Case vbNo
    Me.Saved = True
    Case vbCancel
    Cancel = True
    Exit Sub
    End Select
    End If
    Call DeleteMenu 'Change this to your own subroutine
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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