On 06/30/2014 5:02 PM, symbiosis001 wrote:
incidentally... if the code is also placed in the following

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
gbShuttingDown = True: ShutdownApp
End Sub

Then the menus will unlock when the user swaps to a different workbook and using the code below will lock again when the user swaps back to this workbook.

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
gbShuttingDown = False: ShutdownApp
End Sub

Sym

This changes the structure of your project such that
EnableDisable_BuiltinCtrls is no longer used exclusively at
startup/shutdown. In this case you need to make it generic so it can be
called from anywhere in your project. This means changing your project
as follows...

In a standard module named "m_OpenClose":

Option Explicit

Public Const gsMenusToDisable$ _
= "Worksheet Menu Bar:F&ormat:&Row:&Unhide" _
& ",Worksheet Menu Bar:F&ormat:$Row:&Hide" _
& ",Worksheet Menu Bar:F&ormat:&Column:&Unhide" _
& ",Worksheet Menu Bar:F&ormat:&Column:&Hide" _
& ",Row:&Hide,Row:&Unhide" _
& ",Column:&Hide,Column:&Unhide"
Public gbShuttingDown As Boolean
'...any other global scope variables/constants use by this project


Sub Auto_Open()
'...
'Disable builtin menus/controls not to be used
EnableDisable_BuiltinCtrls gbShuttingDown
'...
End Sub

Sub Auto_Close()
gbShuttingDown = True: ShutdownApp
End Sub

Sub ShutdownApp()
'Restore disabled builtin menus/controls
EnableDisable_BuiltinCtrls gbShuttingDown
'...other shutdown code
End Sub

Sub InitGlobals()
' Used to initialize global variables with runtime values
'...
End Sub

...where this should (IMO) be the default standard module for all
projects. I don't use the 'Microsoft Excel Objects' for any purpose and
so Excel events are handled by a Class Module. I can provide code for
this if you wish, but for now I'll just example how to code your Window
Activate/Deactivate events in the 'ThisWorkbook' component of your
project...

Option Explicit

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
EnableDisable_BuiltinCtrls False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
EnableDisable_BuiltinCtrls True
End Sub


Finally, change EnableDisable_BuiltinCtrls as follows.

Sub EnableDisable_BuiltinCtrls(bEnabled As Boolean)
Dim vSz, vCtls, ctl As Object
On Error Resume Next '//can't toggle disabled context menus
For Each vSz In Split(gsMenusToDisable, ",")
vCtls = Split(vSz, ":")
Select Case UBound(vCtls)
Case Is = 1
CommandBars(vCtls(0)).Controls(vCtls(1)).Enabled = bEnabled
Case Is = 2

CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Enabled =
bEnabled
Case Is = 3

CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Enabled
= bEnabled
Case Is = 4

CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Controls(vCtls(4)).En abled
= bEnabled
Case Is = 5

CommandBars(vCtls(0)).Controls(vCtls(1)).Controls( vCtls(2)).Controls(vCtls(3)).Controls(vCtls(4)).Co ntrols(vCtls(5)).Enabled
= bEnabled
End Select
Next 'vSz
End Sub

--
Regards,
Garry

Free Usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion