Friday, May 08, 2009

Disable Close button using Excel VBA

How to disable close button using VBA

If you want to prevent the user to close the Application, you block the menu items, keypress etc. This snippet will help you in disabling the close button of the application.


Sub DisableExcelMenu()
' Remove Exel Menu Items
Dim hMenu As Long

hMenu = GetSystemMenu(Application.hwnd, 0)
Call DeleteMenu(hMenu, SC_CLOSE, MF_BYCOMMAND)

End Sub

The snippet uses WinAPI functions. Include the following functions to your module:



Public Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long

Public Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long


Declare Function EnableMenuItem Lib "user32" ( _
ByVal hMenu As Long, _
ByVal uIDEnableItem As Long, _
ByVal uEnable As Long) As Long

'Used to find the Outlook icon in the system tray. If present then Outlook is running
Private Declare Function FindWindow _
Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long


Public Const MF_BYCOMMAND = &H0&
Public Const MF_BYPOSITION = &H400&
Public Const SC_ARRANGE = &HF110
Public Const SC_CLOSE = &HF060
Public Const SC_HOTKEY = &HF150
Public Const SC_HSCROLL = &HF080
Public Const SC_KEYMENU = &HF100
Public Const SC_MAXIMIZE = &HF030
Public Const SC_MINIMIZE = &HF020
Public Const SC_MOVE = &HF010
Public Const SC_NEXTWINDOW = &HF040
Public Const SC_PREVWINDOW = &HF050
Public Const SC_RESTORE = &HF120
Public Const SC_SIZE = &HF000
Public Const SC_VSCROLL = &HF070
Public Const SC_TASKLIST = &HF130
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2
Public Const HWND_TOP = 0
Public Const SWP_NOSIZE = &H1
Public Const SWP_NOMOVE = &H2
Public Const GWL_STYLE = (-16)



To enable the close button use

Dim hMenu As Long

hMenu = GetSystemMenu(Application.hwnd, 0)

Call EnableMenuItem(hMenu, SC_CLOSE, MF_BYCOMMAND)


Disabled Close Button Excel

2 comments:

  1. Anonymous5:55 AM

    Enable do not work. :(

    ReplyDelete
  2. Thank you very much, but it's not correct.
    To disable (not delete):
    EnableMenuItem hMenu, SC_CLOSE, FALSE
    To enable:
    EnableMenuItem hMenu, SC_CLOSE, TRUE

    DisableMenuItem deletes the menu item and then you can't reenable it.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group