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