Reloading Custom Menus

Mr Nick · Apr 11th, 2002, 03:51 AM

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 ]

Mr Nick · Apr 11th, 2002, 06:43 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 03:51 AM	#1
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	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 ]

Apr 11th, 2002, 06:43 AM	#2
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	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