Solved

How can I disable the close "X" in the Excel 2007/2010 title bar?

Posted on 2010-09-14
2
380 Views
Last Modified: 2012-05-10
The code below works in thisWorkbook in Excel 2003. I need a way to do this in Excel 2007/2010. Can anyone help?

Option Explicit

'Written: December 24, 2008
'Author:  Leith Ross
'Summary: Enables or Disables the Close "X" on Excel's title bar.

Private Declare Function FindWindow _
  Lib "user32.dll" _
   Alias "FindWindowA" _
    (ByVal lpszClass As String, _
     ByVal lpszWindow As String) As Long
     
Private Declare Function GetSystemMenu _
  Lib "user32.dll" _
  (ByVal hwnd As Long, _
   ByVal bRevert As Long) As Long
    
Private Declare Function RemoveMenu _
  Lib "user32" _
  (ByVal hMenu As Long, _
   ByVal nPosition As Long, _
   ByVal wFlags As Long) As Long
   
Private Declare Function DrawMenuBar _
  Lib "user32.dll" _
    (ByVal hwnd As Long) As Long

Public Sub SetCloseX(ByVal bEnable As Boolean)

  Dim Action As Long
  Dim hMenu As Long
  Dim hwnd As Long
  Dim Ret As Long
  
  Const MF_BYCOMMAND As Long = &H0
  Const SC_CLOSE As Long = &HF060
  
    Action = CLng(bEnable)
    hwnd = FindWindow("XLMAIN", vbNullString)
    hMenu = GetSystemMenu(hwnd, Action)
    
    Ret = RemoveMenu(hMenu, SC_CLOSE, MF_BYCOMMAND)
    Ret = DrawMenuBar(hwnd)

End Sub

Sub RedXworks()
'put this part into workbook_activate, not behind a button
SetCloseX True
End Sub

Sub RedXdisabled()
'put this part into workbook_deactivate, not behind a button
SetCloseX False
End Sub

Open in new window

0
Comment
Question by:wcpoa8
2 Comments
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 33676670
Please the code in Thisworkbook object module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If [your criteria] then
MsgBox "Close Diabled"
Cancel = True
endif
End Sub

Cancel = True which will cancel/disable the close operation.
0
 

Accepted Solution

by:
wcpoa8 earned 0 total points
ID: 33676686
This code works after all. I have my file wrapped in software called LockXLS. That seems to be the problem. Sorry about that. I need to close this question.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question