Disable X to close Excel

[sharky12345]

I'm trying to find a way to close a Workbook through a Macro and prevent closing with the standard X in the corner of the application.

I have this so far;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = False Then
Call MsgBox("You must use the Close button on the menu to exit this file", vbCritical, "Button disabled")
Cancel = True
Exit Sub
End If
End Sub

This works fine, but it also triggers when I use the command I have put on the ribbon which is this;

Application.DisplayAlerts = False
If Workbooks.Count = 1 Then
Application.Quit
ActiveWorkbook.Close SaveChanges:=True
Else
ActiveWorkbook.Close SaveChanges:=True
End If
Application.DisplayAlerts = True

What I need to achieve is to force the user to only be able to close the file using the button I've put on the ribbon and not the X in the corner.

Is there such a way?

 

[VBA Geek]

try this in your workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = Not bClose
    If Not bClose Then MsgBox "You must use the Close button on the menu to exit this file", vbCritical, "Button disabled"
End Sub


Private Sub Workbook_Open()
    bClose = False
End Sub

and in a normal module:

Public bClose As Boolean


Sub closeWorkbook()
bClose = True
ActiveWorkbook.Close True
End Sub

 

[sharky12345]

Hi - thanks but this doesn't work - I still get the warning when I try to close the workbook through my ribbon button.

 

[VBA Geek]

the sub linked to your button is

Sub closeWorkbook()
bClose = True
ActiveWorkbook.Close True
End Sub

 

[sharky12345]

Ahh - I understand now.

Thanks!