Disable 'X' on Worksheet

[Pacman52]

Hi all hopefully a simple one for some of you but I just can't seem to get this sorted and my net searches for help have left me even more confused !

So I have a workbook that I want to force a user to have to click a button to close the workbook.
The only thing (apart from a worksheet) visible is the Excel title bar at the very top of a window containing the usual buttons to minimise, restore and close.

I have the code below set to a custom Exit button on a worksheet which works fine for what I need.

Sub ZZ_EXIT()

    ThisWorkbook.Save
    ThisWorkbook.Close
        
End Sub

Logically I would say I need to disable the close button when the workbook opens so that a user can't do anything with it as they simply can't press it (perhaps a msgbox with "Button is disabled") If my logic is correct then I have the following code on Workbook Open, is there something I can add to this?

Private Sub Workbook_Open(

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",False)"
        .WindowState = xlMaximized
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .DisplayStatusBar = False
        .DisplayFormulaBar = False
        .DisplayScrollBars = False

    End With

    With ActiveWindow
        .DisplayWorkbookTabs = False
        .DisplayRuler = False
        .DisplayHeadings = False
    End With

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End sub

Many thanks in advance for any help or replies

Paul

 

[Scott Huish]

The only thing your button does is save and close the workbook. So wouldn't it also work if you saved the workbook no matter how it was closed?

 

[Pacman52]

The only thing your button does is save and close the workbook. So wouldn't it also work if you saved the workbook no matter how it was closed?

Thanks for the reply and sorry I should have been clearer in my question.

So yes in theory your point is totally correct but 99.9% time a user will have added some data to the workbook so by having the close button enabled it will always prompt for a Save As where as my button simply saves everything and closes the WB.

Leaving the close button available for use just adds an element of them not saving the WB as it is - if that makes sense !

 

[Scott Huish]

That could be programmed to not happen, but I'm not sure how to do exactly as your asking, so I'll leave it to someone else

 

[Pacman52]

Just to let you know and for anyone else looking for a solution to this. after a bit of research I found out it was actually really simple to do this, all that's needed is to add Cancel = True to the before workbook close - as below (the msgbox is of course just for my needs and doesn't have to be included in the sub)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Cancel = True
    MsgBox "Plesase use the Exit button on the Main Menu.", vbInformation, "Disabled"

End Sub