Disable 'X' on Worksheet

Pacman52

Active Member
Joined
Jan 29, 2009
Messages
420
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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?

VBA Code:
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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?
 
Upvote 0
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 !
 
Upvote 0
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
 
Upvote 0
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)

VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

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

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,224,568
Messages
6,179,580
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top