disable x to close in excel sheet

[narsing rao]

Hi all,

i have excel sheet in that i have created form control button which save & close the excel.

but few of my user not using the button but using X close at top right corner close option.

i want my user to using this button so that in one instance they can save and close the excel , i want to disable this X option from excel.

 

[Belleke]

Hi,
I think you want something like this.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
        Cancel = True
        MsgBox "The X is disabled, please use the Close Command Button.", vbCritical
    End If
End Sub

 

[narsing rao]

it doesnt worked .. i have created form control button named save & close to save and close excel sheet in one instance. it is created in excel sheet only there is no userforms used.i have attached the pic and assigned below code to button

Sub SaveAndClose()
ActiveWorkbook.Save
ActiveWorkbook.Close
    'ActiveWorkbook.Close SaveChanges:=True
    Workbooks("Break_tr1.xlsm").Close
Application.Quit
End Sub
[\code]

 

[Belleke]

I tought that you had a userform

i have created form

Try this instead in ThisWorbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If CloseMode = 0 Then Cancel = True
MsgBox "The X is disabled,please use the Save & Close button"
End Sub

 

[narsing rao]

Hi Thanks for the code ..
now the X disabled but when i click on Save & close button still its giving me the same message "The X is disabled,please use the Save & Close button"

 

[narsing rao]

Hi Thanks for the code ..
now the X disabled but when i click on Save & close button still its giving me the same message "The X is disabled,please use the Save & Close button"

and the excel sheet is not getting closed

 

[Belleke]

Try this in the button code

Sub SaveAndClose()
If CloseMode = 0 Then Cancel = False
'rest of your code
end sub

 

[narsing rao]

Hi ..still i am geting same message " The X is disabled,please use the Save & Close button" and after clicking save & close button it shows same message and excel doesn't close

 

[Belleke]

Hi, this works, tested this time

Public myRao As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Run from "ThisWorkbook" module!
If myRao = True Then Exit Sub
Application.DisplayAlerts = False
Cancel = True
MsgBox "The X is disabled,please use the Save & Close button", vbCritical, "Error"
End Sub
Sub SaveAndClose()
'Run from "ThisWorkbook" module!
myRao = True
ActiveWorkbook.Close SaveChanges:=True
Application.DisplayAlerts = False
ActiveWindow.Close False
End Sub

 

[narsing rao]

i am doing anything wrong...still i am getting same error ""The X is disabled,please use the Save & Close button" while clicking ion button

Public myRao AsBoolean
PrivateSub Workbook_BeforeClose(Cancel AsBoolean)
'Run from "ThisWorkbook" module!If myRao = TrueThenExitSub
Application.DisplayAlerts = False
Cancel = True
MsgBox "The X is disabled,please use the Save & Close button", vbCritical, "Error"
EndSub

i copied this in thisworkbook

and remaining code in code Module.

 

[Belleke]

All the code goes in ThisWorkbook module
see attached (working example)

 

[narsing rao]

Thanks a ton ...its working now ...solved

 

[Belleke]

Thanks for the feedback
Glad to help.