Shortcut To Temporary Enable Previously Disabled Workbook Close Function [X]

[Ark68]

I have this code that I use to disable the close function of the workbook close button [X]. (thank you ZVI).
Can anyone suggest a way that I can create a keyboard shortcut that I could use to temporarily enable the feature? It's implemented for shared user protection, but is awkward for me when testing the workbook. If I could just have a shortcut to enable, rather than going intot he code and commenting everything out would be handy.

 

[ZVI]

Hi,
Put this code: to Thisworkbook module:

Private Sub Workbook_Open()
  Application.OnKey "%{UP}", "EnableXExit"    ' Alt+Up
  Application.OnKey "%{DOWN}", "DisableXExit" ' Alt_Down
End Sub

The below code goes to standard module:

Sub EnableXExit()
  IsOnExit = True
  'Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
  MsgBox "XExit is enabled"
End Sub

Sub DisableXExit()
  IsOnExit = False
  'Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
  MsgBox "XExit is disabled"
End Sub

To disable shortcuts before this workbook closing, insert these 2 code lines into Sub front_exit below the line Application.ScreenUpdating = True

    Application.OnKey "%{UP}", ""
    Application.OnKey "%{DOWN}", ""

Select & run Workbook_Open.
Then use Alt+Up and Alt+Down shortcuts

Note: code for the [EXIT] button still works and can modify IsOnExit variable as well.

 

[Ark68]

Hi Vladimir my friend. Thank you for sharing your solution.

Before I go about monkeying around with my code that is working well, (this is simply a convenience thing and not necessary for the end user experience), may I confirm...

I use this line

Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"

in a procedure I use called "wkbk_on_open" that executes in my workbook open event. This line accompanies workbook feature formatting like hiding scrollbars, formula bars, scroll bars etc, and of course removing the ribbon. Even after executed the window still has the minimize, maximize and close buttons.

So, I anticipate (and my apologies if my assumptions are incorrect) that your shortcuts will simply reveal and conceal the taskbar (ribbon).

With the [X] button disabled in function in the workbook close event (that you so kindly helped figure out for me), I'm hoping for a shortcut I can use to bypass this limitation. (as the developer to bypass in the interim instead of having to always exit via the interface ... simply a time savings for me)

 

[ZVI]

Then the ExitX functionality is enabled via Alt+UP, the Excel reacts on clicking its [X] button as usual.

To hide/show ribbon and other interface things. look at post #2 onto commented lines:

'Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True/False)"

Just uncomment it to restore/hide ribbon via shortcuts, like this:

Sub EnableXExit()
  IsOnExit = True
  Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
  ' put here the code to restore all hidden toolbars etc.
  ' ...
  MsgBox "XExit is enabled"
End Sub

Sub DisableXExit()
  IsOnExit = False
  Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
  ' put here the code to hide toolbars etc.
  ' ...
  MsgBox "XExit is disabled"
End Sub

Write your code to hide/restore those interface things just below the comment: ' put here the code ...
If it's not enough then please describe step-by-step your actions and what should happen.

 

[Ark68]

Ah, ok. My apologies, I missed the fact you had the hide/show ribbon line commented out.
Works like a charm Vladimir!! Thank you so much.

 

[ZVI]

That is good, please post back if something is still wrong.