auto_close and removing menus

[Guest]

The job that I want auto_close to do is to remove menus when the user wants
to close Excel.

My problem is when the user types information in a worksheet and doesn't
save it. Excel will call my auto_close method and I remove the menus that I
created. In this case the user will get a chance to save her file. If the
user presses "Cancel" at this point, Excel stays open. Now Excel is still
open and my menus are removed.

Below is my script. Please let me know if I should be doing instead.
Otherwise I will put a dialog box that says "Do not press Cancel idiot."
(yes, kidding).

Thanks in advance for any suggestions...

------ TIETools.xla Startup Script ------------
Option Explicit

Private Const MY_MENU As String = "&My Menu"
Private Const MY_MENU_ITEM As String = "&Do It"



Sub Auto_Open()
Dim mnu As CommandBarControl
Dim item As CommandBarControl

On Error Resume Next
' Determine whether command bar already exists.
Set mnu = CommandBars("Worksheet Menu Bar").Controls(MY_MENU)
' If command bar does not exist, create it.
If mnu Is Nothing Then
Err.Clear
Set mnu = CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup)

mnu.Caption = MY_MENU

Set item = mnu.Controls.Add
With item
.Style = msoControlButton
.Caption = MY_MENU_ITEM
.Tag = MY_MENU_ITEM
.OnAction = "TIETools.Main.DoSomething"
End With
End If
ReDim FieldList(1)
End Sub

Private Sub DoSomething()
MsgBox "do it", vbOKOnly, "DoSomething"
End Sub

Sub Auto_Close()
On Error Resume Next
' Delete command bar, if it exists.
CommandBars("Worksheet Menu Bar").Controls(MY_MENU).Delete
End Sub
------ TIETools.xla ------------

 

[Anne Troy]

Either make it a Workbook Before Close or use proper punctuation: Do not
press Cancel, Idiot!
*******************
~Anne Troy

www.OfficeArticles.com