Excell menu options

Posted by Damien on June 26, 2001 8:36 PM

i have a auto open macro which takes away most of the menues but what I don't know is can you take way an option in the main menu or if the exit or X can have a macro assigned to them so they runa macro or take way the use of them as I have to have the user do something in my workbook each time they close it but I can't trust them any ideas

Posted by Dax on June 27, 2001 4:19 AM

These two macros enable and disable the Exit button from the File menu and the close X in the top right hand corner. They're not the most simple of macros as they use API calls but they should do what you want.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Private Const SC_CLOSE As Long = &HF060
Sub DisableExits()
Dim Combar As CommandBar, Cont As CommandBarControl
Dim lButtonIndex As Long, lXLHandle As Long, lSysMenu As Long
Set Combar = CommandBars("File")
For Each Cont In Combar.Controls
If Cont.Caption = "E&xit" Then
lButtonIndex = Cont.Index
Exit For
End If
Next
If lButtonIndex = 0 Then Exit Sub 'An error has occurred
Combar.Controls(lButtonIndex).Enabled = False
lXLHandle = FindWindow("XLMAIN", Application.Caption)
lSysMenu = GetSystemMenu(lXLHandle, 0)
DeleteMenu lSysMenu, SC_CLOSE, 0&
End Sub

Sub EnableExits()
Dim Combar As CommandBar, Cont As CommandBarControl
Dim lButtonIndex As Long
Set Combar = CommandBars("File")
For Each Cont In Combar.Controls
If Cont.Caption = "E&xit" Then
lButtonIndex = Cont.Index
Exit For
End If
Next
Combar.Controls(lButtonIndex).Enabled = True
lXLHandle = FindWindow("XLMAIN", Application.Caption)
lSysMenu = GetSystemMenu(lXLHandle, 1)
End Sub

HTH,
Dax.