+ Reply to Thread
Results 1 to 10 of 10

Code Halt - prevents reset of the AutomationSecurity level

  1. #1
    Walt
    Guest

    Code Halt - prevents reset of the AutomationSecurity level

    Hi,

    Starting with a copy of the sample from the help system, I modified it
    slightly as you can see below. I find that not only is the code in an
    unsigned project prevented from running (As intended), but all code,
    even in the project 'controlling' the open, is immediately halted.
    This prevents the reset of the AutomationSecurity level.

    'MODIFIED FROM VBA HELP SAMPLE
    Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    Dim secAuto As MsoAutomationSecurity
    secAuto = Application.AutomationSecurity

    'MODIFIED FROM FORCEDISABLE TO ByUI
    Application.AutomationSecurity = msoAutomationSecurityByUI
    'MODIFIED THIS LINE
    Application.Dialogs(xlDialogOpen).Show

    'CODE HALTS, & NEVER GETS TO NEXT LINE
    'IF ANY FILES OPENED HAVE CODE
    'PREVENTED FROM RUNNING BY THE
    'MACRO SECURITY
    Application.AutomationSecurity = secAuto
    End Sub

    Does anyone know a better way to do this?

    Best Regards,
    Walt Weber


  2. #2
    Jim Thomlinson
    Guest

    RE: Code Halt - prevents reset of the AutomationSecurity level

    You can't change the security level through VBA. It is designed that way.
    Otherwise every virus would just change the security level and thereby bypass
    the whole point of having security in the first place.

    HTH

    "Walt" wrote:

    > Hi,
    >
    > Starting with a copy of the sample from the help system, I modified it
    > slightly as you can see below. I find that not only is the code in an
    > unsigned project prevented from running (As intended), but all code,
    > even in the project 'controlling' the open, is immediately halted.
    > This prevents the reset of the AutomationSecurity level.
    >
    > 'MODIFIED FROM VBA HELP SAMPLE
    > Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    > Dim secAuto As MsoAutomationSecurity
    > secAuto = Application.AutomationSecurity
    >
    > 'MODIFIED FROM FORCEDISABLE TO ByUI
    > Application.AutomationSecurity = msoAutomationSecurityByUI
    > 'MODIFIED THIS LINE
    > Application.Dialogs(xlDialogOpen).Show
    >
    > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > 'IF ANY FILES OPENED HAVE CODE
    > 'PREVENTED FROM RUNNING BY THE
    > 'MACRO SECURITY
    > Application.AutomationSecurity = secAuto
    > End Sub
    >
    > Does anyone know a better way to do this?
    >
    > Best Regards,
    > Walt Weber
    >
    >


  3. #3
    Walt
    Guest

    Re: Code Halt - prevents reset of the AutomationSecurity level

    Hi Jim,

    You might want to look up the 'AutomationSecurity Property' in the
    Excel VBA help system for how it works with regard to programmatically
    opening files. There are also meaningful references to it and
    discussion of it available via news groups search. My problem is that
    it seems to hit a snag in the way it works when I want it to behave as
    though the user were selecting 'Open' from the 'File' menu with his
    normal macro security level in place (All code halts if the code in the
    user selected file is prohibited from running), then continue
    processing. Note that "every virus" couldn't "just change the
    security level" if it hadn't already gotten in and started doing its
    thing.

    I do appreciate your concern.

    Best Regards,
    Walt



    Jim Thomlinson wrote:
    > You can't change the security level through VBA. It is designed that

    way.
    > Otherwise every virus would just change the security level and

    thereby bypass
    > the whole point of having security in the first place.
    >
    > HTH
    >
    > "Walt" wrote:
    >
    > > Hi,
    > >
    > > Starting with a copy of the sample from the help system, I modified

    it
    > > slightly as you can see below. I find that not only is the code

    in an
    > > unsigned project prevented from running (As intended), but all

    code,
    > > even in the project 'controlling' the open, is immediately halted.
    > > This prevents the reset of the AutomationSecurity level.
    > >
    > > 'MODIFIED FROM VBA HELP SAMPLE
    > > Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    > > Dim secAuto As MsoAutomationSecurity
    > > secAuto = Application.AutomationSecurity
    > >
    > > 'MODIFIED FROM FORCEDISABLE TO ByUI
    > > Application.AutomationSecurity = msoAutomationSecurityByUI
    > > 'MODIFIED THIS LINE
    > > Application.Dialogs(xlDialogOpen).Show
    > >
    > > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > > 'IF ANY FILES OPENED HAVE CODE
    > > 'PREVENTED FROM RUNNING BY THE
    > > 'MACRO SECURITY
    > > Application.AutomationSecurity = secAuto
    > > End Sub
    > >
    > > Does anyone know a better way to do this?
    > >
    > > Best Regards,
    > > Walt Weber
    > >
    > >



  4. #4
    Simon Murphy
    Guest

    RE: Code Halt - prevents reset of the AutomationSecurity level

    Walt
    I had the same problem
    In the end I compiled the caller into a VB6.0 exe which worked fine. It
    does seem to halt the whole of vba.
    In the vba version I stripped the automationSecurity stuff and relied on
    disabling events and calculation, on the basis that this would stop any code
    being triggered.

    cheers
    Simon

    "Walt" wrote:

    > Hi,
    >
    > Starting with a copy of the sample from the help system, I modified it
    > slightly as you can see below. I find that not only is the code in an
    > unsigned project prevented from running (As intended), but all code,
    > even in the project 'controlling' the open, is immediately halted.
    > This prevents the reset of the AutomationSecurity level.
    >
    > 'MODIFIED FROM VBA HELP SAMPLE
    > Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    > Dim secAuto As MsoAutomationSecurity
    > secAuto = Application.AutomationSecurity
    >
    > 'MODIFIED FROM FORCEDISABLE TO ByUI
    > Application.AutomationSecurity = msoAutomationSecurityByUI
    > 'MODIFIED THIS LINE
    > Application.Dialogs(xlDialogOpen).Show
    >
    > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > 'IF ANY FILES OPENED HAVE CODE
    > 'PREVENTED FROM RUNNING BY THE
    > 'MACRO SECURITY
    > Application.AutomationSecurity = secAuto
    > End Sub
    >
    > Does anyone know a better way to do this?
    >
    > Best Regards,
    > Walt Weber
    >
    >


  5. #5
    Walt
    Guest

    Re: Code Halt - prevents reset of the AutomationSecurity level

    Hi Simon,

    It's nice to have confirmation, though I'd been hoping the
    documentation neglected to mention some library reference I should
    establish.

    The idea of "disabling events and calculation" had not occurred to me,
    and I can see that might be OK if I just wanted to read some content
    and then close the file.

    Thank you.

    Best Regards,
    Walt Weber


    Simon Murphy wrote:
    > Walt
    > I had the same problem
    > In the end I compiled the caller into a VB6.0 exe which worked fine.

    It
    > does seem to halt the whole of vba.
    > In the vba version I stripped the automationSecurity stuff and relied

    on
    > disabling events and calculation, on the basis that this would stop

    any code
    > being triggered.
    >
    > cheers
    > Simon
    >
    > "Walt" wrote:
    >
    > > Hi,
    > >
    > > Starting with a copy of the sample from the help system, I modified

    it
    > > slightly as you can see below. I find that not only is the code

    in an
    > > unsigned project prevented from running (As intended), but all

    code,
    > > even in the project 'controlling' the open, is immediately halted.
    > > This prevents the reset of the AutomationSecurity level.
    > >
    > > 'MODIFIED FROM VBA HELP SAMPLE
    > > Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    > > Dim secAuto As MsoAutomationSecurity
    > > secAuto = Application.AutomationSecurity
    > >
    > > 'MODIFIED FROM FORCEDISABLE TO ByUI
    > > Application.AutomationSecurity = msoAutomationSecurityByUI
    > > 'MODIFIED THIS LINE
    > > Application.Dialogs(xlDialogOpen).Show
    > >
    > > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > > 'IF ANY FILES OPENED HAVE CODE
    > > 'PREVENTED FROM RUNNING BY THE
    > > 'MACRO SECURITY
    > > Application.AutomationSecurity = secAuto
    > > End Sub
    > >
    > > Does anyone know a better way to do this?
    > >
    > > Best Regards,
    > > Walt Weber
    > >
    > >



  6. #6
    Tushar Mehta
    Guest

    Re: Code Halt - prevents reset of the AutomationSecurity level

    Well, I spent the better part of the last hour trying to make this
    work. Tried a class with a 'withevents' application, tried a 'on error
    resume next' (which the documentation at
    http://support.microsoft.com/default...b;en-us;317405 implies
    -- but doesn't really state -- should work), tried a modeless userform,
    and tried a OnTime procedure.

    What seems to happen is that XL/VBA causes a fault in the code that
    resets the module; however, the fault is so 'hard' that the on error
    clause doesn't work (but not so hard so as to cause XL to crash).
    Since all global variables are reset, you cannot save the current
    setting in a global variable.

    Bottom line: The procedure specified in the OnTime method does get
    executed. While I tested it with something trivial (see below), in
    reality it would have to be much more sophisticated. It would have to
    detect if either of the open dialog box or the security warning dialog
    box is still visible. If so, it would have to reschedule itself for a
    later time.

    Public secAuto As MsoAutomationSecurity
    Sub resetSec()
    Application.AutomationSecurity = msoAutomationSecurityLow
    MsgBox "In resetSec"
    End Sub
    Sub testSecurity() 'TEST WITH SECURITY DG SET TO HIGH
    secAuto = Application.AutomationSecurity

    Application.AutomationSecurity = msoAutomationSecurityByUI
    Application.OnTime Now() + TimeSerial(0, 0, 10), "resetSec"
    Application.Dialogs(xlDialogOpen).Show
    If Err.Number <> 0 Then MsgBox Err.Description
    'CODE HALTS, & NEVER GETS TO NEXT LINE
    'IF ANY FILES OPENED HAVE CODE
    'PREVENTED FROM RUNNING BY THE
    'MACRO SECURITY
    MsgBox "all done"
    Application.AutomationSecurity = secAuto
    End Sub

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <1111541013.796146.260020@z14g2000cwz.googlegroups.com>,
    xlswalt@aol.com says...
    > Hi,
    >
    > Starting with a copy of the sample from the help system, I modified it
    > slightly as you can see below. I find that not only is the code in an
    > unsigned project prevented from running (As intended), but all code,
    > even in the project 'controlling' the open, is immediately halted.
    > This prevents the reset of the AutomationSecurity level.
    >
    > 'MODIFIED FROM VBA HELP SAMPLE
    > Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    > Dim secAuto As MsoAutomationSecurity
    > secAuto = Application.AutomationSecurity
    >
    > 'MODIFIED FROM FORCEDISABLE TO ByUI
    > Application.AutomationSecurity = msoAutomationSecurityByUI
    > 'MODIFIED THIS LINE
    > Application.Dialogs(xlDialogOpen).Show
    >
    > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > 'IF ANY FILES OPENED HAVE CODE
    > 'PREVENTED FROM RUNNING BY THE
    > 'MACRO SECURITY
    > Application.AutomationSecurity = secAuto
    > End Sub
    >
    > Does anyone know a better way to do this?
    >
    > Best Regards,
    > Walt Weber
    >
    >


  7. #7
    Walt
    Guest

    Re: Code Halt - prevents reset of the AutomationSecurity level

    Hi Tushar,

    Somehow, a response I sent hours ago has not listed here.

    I want to THANK YOU for your effort and the resulting better
    understanding.

    I had not thought of the 'OnTime' approach, though I did try 'On Error
    Resume Next' with the same results.

    Your discovery that the "global variables are reset" is something that
    I had not noticed yet. That, for the most part, makes
    'msoAutomationSecurityByUI' useless.

    Best Regards,
    Walt Weber

    Tushar Mehta wrote:
    > Well, I spent the better part of the last hour trying to make this
    > work. Tried a class with a 'withevents' application, tried a 'on

    error
    > resume next' (which the documentation at
    > http://support.microsoft.com/default...b;en-us;317405

    implies
    > -- but doesn't really state -- should work), tried a modeless

    userform,
    > and tried a OnTime procedure.
    >
    > What seems to happen is that XL/VBA causes a fault in the code that
    > resets the module; however, the fault is so 'hard' that the on error
    > clause doesn't work (but not so hard so as to cause XL to crash).
    > Since all global variables are reset, you cannot save the current
    > setting in a global variable.
    >
    > Bottom line: The procedure specified in the OnTime method does get
    > executed. While I tested it with something trivial (see below), in
    > reality it would have to be much more sophisticated. It would have

    to
    > detect if either of the open dialog box or the security warning

    dialog
    > box is still visible. If so, it would have to reschedule itself for

    a
    > later time.
    >
    > Public secAuto As MsoAutomationSecurity
    > Sub resetSec()
    > Application.AutomationSecurity = msoAutomationSecurityLow
    > MsgBox "In resetSec"
    > End Sub
    > Sub testSecurity() 'TEST WITH SECURITY DG SET TO HIGH
    > secAuto = Application.AutomationSecurity
    >
    > Application.AutomationSecurity = msoAutomationSecurityByUI
    > Application.OnTime Now() + TimeSerial(0, 0, 10), "resetSec"
    > Application.Dialogs(xlDialogOpen).Show
    > If Err.Number <> 0 Then MsgBox Err.Description
    > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > 'IF ANY FILES OPENED HAVE CODE
    > 'PREVENTED FROM RUNNING BY THE
    > 'MACRO SECURITY
    > MsgBox "all done"
    > Application.AutomationSecurity = secAuto
    > End Sub
    >
    > --
    > Regards,
    >
    > Tushar Mehta
    > www.tushar-mehta.com
    > Excel, PowerPoint, and VBA add-ins, tutorials
    > Custom MS Office productivity solutions
    >
    > In article <1111541013.796146.260020@z14g2000cwz.googlegroups.com>,
    > xlswalt@aol.com says...
    > > Hi,
    > >
    > > Starting with a copy of the sample from the help system, I modified

    it
    > > slightly as you can see below. I find that not only is the code

    in an
    > > unsigned project prevented from running (As intended), but all

    code,
    > > even in the project 'controlling' the open, is immediately halted.
    > > This prevents the reset of the AutomationSecurity level.
    > >
    > > 'MODIFIED FROM VBA HELP SAMPLE
    > > Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    > > Dim secAuto As MsoAutomationSecurity
    > > secAuto = Application.AutomationSecurity
    > >
    > > 'MODIFIED FROM FORCEDISABLE TO ByUI
    > > Application.AutomationSecurity = msoAutomationSecurityByUI
    > > 'MODIFIED THIS LINE
    > > Application.Dialogs(xlDialogOpen).Show
    > >
    > > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > > 'IF ANY FILES OPENED HAVE CODE
    > > 'PREVENTED FROM RUNNING BY THE
    > > 'MACRO SECURITY
    > > Application.AutomationSecurity = secAuto
    > > End Sub
    > >
    > > Does anyone know a better way to do this?
    > >
    > > Best Regards,
    > > Walt Weber
    > >
    > >



  8. #8
    Tushar Mehta
    Guest

    Re: Code Halt - prevents reset of the AutomationSecurity level

    Yes, that's what I thought yesterday that 'msoAutomationSecurityByUI'
    was useless. Now, I am ambivalent.

    First, a simple workaround using 2 booleans:

    Option Explicit

    Dim StillWaiting As Boolean, DialogClosed As Boolean
    Sub resetSec()
    If StillWaiting Then
    Application.OnTime Now() + TimeSerial(0, 0, 1), "resetSec"
    Exit Sub
    End If
    If Not DialogClosed Then
    MsgBox "User chose 'disable macros'"
    Else
    MsgBox "User chose 'enable macros'"
    End If
    Application.AutomationSecurity = msoAutomationSecurityLow
    End Sub
    Sub testSecurity() 'TEST WITH SECURITY DG SET TO HIGH
    'secAuto = Application.AutomationSecurity
    'need to save in the registry

    Application.AutomationSecurity = msoAutomationSecurityByUI
    StillWaiting = True
    resetSec
    Application.Dialogs(xlDialogOpen).Show
    'Because of a bug (feature?) _
    CODE HALTS, & NEVER GETS TO NEXT LINE _
    IF ANY FILES OPENED HAVE CODE _
    PREVENTED FROM RUNNING BY THE _
    MACRO SECURITY
    DialogClosed = True: StillWaiting = False
    End Sub

    Another workaround would be to create your own dialog box and ask the
    user ahead of time whether the code, if any, in file xxx should or
    should not be enabled.

    Why the ambivalence? Two reasons. I am not sure of the value of this
    ask-the-user approach. There must be a reason for opening the file.
    Why can't the developer decide if macros must be enabled? The other
    reason is that I am a strong proponent of the separation of code and
    data. So, I would be hard pressed to have a system wherein a workbook
    that a user opens contains code. [It should be in an add-in.] Of
    course, you know your system better than I... {g}

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <1111613162.922644.139470@f14g2000cwb.googlegroups.com>,
    xlswalt@aol.com says...
    > Hi Tushar,
    >
    > Somehow, a response I sent hours ago has not listed here.
    >
    > I want to THANK YOU for your effort and the resulting better
    > understanding.
    >
    > I had not thought of the 'OnTime' approach, though I did try 'On Error
    > Resume Next' with the same results.
    >
    > Your discovery that the "global variables are reset" is something that
    > I had not noticed yet. That, for the most part, makes
    > 'msoAutomationSecurityByUI' useless.
    >
    > Best Regards,
    > Walt Weber
    >
    > Tushar Mehta wrote:
    > > Well, I spent the better part of the last hour trying to make this
    > > work. Tried a class with a 'withevents' application, tried a 'on

    > error
    > > resume next' (which the documentation at
    > > http://support.microsoft.com/default...b;en-us;317405

    > implies
    > > -- but doesn't really state -- should work), tried a modeless

    > userform,
    > > and tried a OnTime procedure.
    > >
    > > What seems to happen is that XL/VBA causes a fault in the code that
    > > resets the module; however, the fault is so 'hard' that the on error
    > > clause doesn't work (but not so hard so as to cause XL to crash).
    > > Since all global variables are reset, you cannot save the current
    > > setting in a global variable.
    > >
    > > Bottom line: The procedure specified in the OnTime method does get
    > > executed. While I tested it with something trivial (see below), in
    > > reality it would have to be much more sophisticated. It would have

    > to
    > > detect if either of the open dialog box or the security warning

    > dialog
    > > box is still visible. If so, it would have to reschedule itself for

    > a
    > > later time.
    > >
    > > Public secAuto As MsoAutomationSecurity
    > > Sub resetSec()
    > > Application.AutomationSecurity = msoAutomationSecurityLow
    > > MsgBox "In resetSec"
    > > End Sub
    > > Sub testSecurity() 'TEST WITH SECURITY DG SET TO HIGH
    > > secAuto = Application.AutomationSecurity
    > >
    > > Application.AutomationSecurity = msoAutomationSecurityByUI
    > > Application.OnTime Now() + TimeSerial(0, 0, 10), "resetSec"
    > > Application.Dialogs(xlDialogOpen).Show
    > > If Err.Number <> 0 Then MsgBox Err.Description
    > > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > > 'IF ANY FILES OPENED HAVE CODE
    > > 'PREVENTED FROM RUNNING BY THE
    > > 'MACRO SECURITY
    > > MsgBox "all done"
    > > Application.AutomationSecurity = secAuto
    > > End Sub
    > >
    > > --
    > > Regards,
    > >
    > > Tushar Mehta
    > > www.tushar-mehta.com
    > > Excel, PowerPoint, and VBA add-ins, tutorials
    > > Custom MS Office productivity solutions
    > >
    > > In article <1111541013.796146.260020@z14g2000cwz.googlegroups.com>,
    > > xlswalt@aol.com says...
    > > > Hi,
    > > >
    > > > Starting with a copy of the sample from the help system, I modified

    > it
    > > > slightly as you can see below. I find that not only is the code

    > in an
    > > > unsigned project prevented from running (As intended), but all

    > code,
    > > > even in the project 'controlling' the open, is immediately halted.
    > > > This prevents the reset of the AutomationSecurity level.
    > > >
    > > > 'MODIFIED FROM VBA HELP SAMPLE
    > > > Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    > > > Dim secAuto As MsoAutomationSecurity
    > > > secAuto = Application.AutomationSecurity
    > > >
    > > > 'MODIFIED FROM FORCEDISABLE TO ByUI
    > > > Application.AutomationSecurity = msoAutomationSecurityByUI
    > > > 'MODIFIED THIS LINE
    > > > Application.Dialogs(xlDialogOpen).Show
    > > >
    > > > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > > > 'IF ANY FILES OPENED HAVE CODE
    > > > 'PREVENTED FROM RUNNING BY THE
    > > > 'MACRO SECURITY
    > > > Application.AutomationSecurity = secAuto
    > > > End Sub
    > > >
    > > > Does anyone know a better way to do this?
    > > >
    > > > Best Regards,
    > > > Walt Weber
    > > >
    > > >

    >
    >


  9. #9
    Walt
    Guest

    Re: Code Halt - prevents reset of the AutomationSecurity level

    Hi Tushar,

    Your tenacity and its result are impressive.

    For the time being, I'll defer using this approach until later Excel
    versions when the 'msoAutomationSecurityByUI' bug is fixed. Loading
    all global variables to the registry to preserve their values and then
    restoring them seems impractical.

    In the circumstance under consideration, the files the user should open
    are, at some locations, used as stand alone applications and in all
    locations can have multiple copies with differing scenarios for the
    user to choose among. These have been in use a long time - one will
    finally include a userform vs. dialog sheet with the next release.

    Thank you again for your thoughtful responses.

    Best Regards,
    Walt Weber

    Tushar Mehta wrote:
    > Yes, that's what I thought yesterday that 'msoAutomationSecurityByUI'


    > was useless. Now, I am ambivalent.
    >
    > First, a simple workaround using 2 booleans:
    >
    > Option Explicit
    >
    > Dim StillWaiting As Boolean, DialogClosed As Boolean
    > Sub resetSec()
    > If StillWaiting Then
    > Application.OnTime Now() + TimeSerial(0, 0, 1), "resetSec"
    > Exit Sub
    > End If
    > If Not DialogClosed Then
    > MsgBox "User chose 'disable macros'"
    > Else
    > MsgBox "User chose 'enable macros'"
    > End If
    > Application.AutomationSecurity = msoAutomationSecurityLow
    > End Sub
    > Sub testSecurity() 'TEST WITH SECURITY DG SET TO HIGH
    > 'secAuto = Application.AutomationSecurity
    > 'need to save in the registry
    >
    > Application.AutomationSecurity = msoAutomationSecurityByUI
    > StillWaiting = True
    > resetSec
    > Application.Dialogs(xlDialogOpen).Show
    > 'Because of a bug (feature?) _
    > CODE HALTS, & NEVER GETS TO NEXT LINE _
    > IF ANY FILES OPENED HAVE CODE _
    > PREVENTED FROM RUNNING BY THE _
    > MACRO SECURITY
    > DialogClosed = True: StillWaiting = False
    > End Sub
    >
    > Another workaround would be to create your own dialog box and ask the


    > user ahead of time whether the code, if any, in file xxx should or
    > should not be enabled.
    >
    > Why the ambivalence? Two reasons. I am not sure of the value of

    this
    > ask-the-user approach. There must be a reason for opening the file.
    > Why can't the developer decide if macros must be enabled? The other
    > reason is that I am a strong proponent of the separation of code and
    > data. So, I would be hard pressed to have a system wherein a

    workbook
    > that a user opens contains code. [It should be in an add-in.] Of
    > course, you know your system better than I... {g}
    >
    > --
    > Regards,
    >
    > Tushar Mehta
    > www.tushar-mehta.com
    > Excel, PowerPoint, and VBA add-ins, tutorials
    > Custom MS Office productivity solutions
    >
    > In article <1111613162.922644.139470@f14g2000cwb.googlegroups.com>,
    > xlswalt@aol.com says...
    > > Hi Tushar,
    > >
    > > Somehow, a response I sent hours ago has not listed here.
    > >
    > > I want to THANK YOU for your effort and the resulting better
    > > understanding.
    > >
    > > I had not thought of the 'OnTime' approach, though I did try 'On

    Error
    > > Resume Next' with the same results.
    > >
    > > Your discovery that the "global variables are reset" is something

    that
    > > I had not noticed yet. That, for the most part, makes
    > > 'msoAutomationSecurityByUI' useless.
    > >
    > > Best Regards,
    > > Walt Weber
    > >
    > > Tushar Mehta wrote:
    > > > Well, I spent the better part of the last hour trying to make

    this
    > > > work. Tried a class with a 'withevents' application, tried a 'on

    > > error
    > > > resume next' (which the documentation at
    > > > http://support.microsoft.com/default...b;en-us;317405

    > > implies
    > > > -- but doesn't really state -- should work), tried a modeless

    > > userform,
    > > > and tried a OnTime procedure.
    > > >
    > > > What seems to happen is that XL/VBA causes a fault in the code

    that
    > > > resets the module; however, the fault is so 'hard' that the on

    error
    > > > clause doesn't work (but not so hard so as to cause XL to crash).
    > > > Since all global variables are reset, you cannot save the current
    > > > setting in a global variable.
    > > >
    > > > Bottom line: The procedure specified in the OnTime method does

    get
    > > > executed. While I tested it with something trivial (see below),

    in
    > > > reality it would have to be much more sophisticated. It would

    have
    > > to
    > > > detect if either of the open dialog box or the security warning

    > > dialog
    > > > box is still visible. If so, it would have to reschedule itself

    for
    > > a
    > > > later time.
    > > >
    > > > Public secAuto As MsoAutomationSecurity
    > > > Sub resetSec()
    > > > Application.AutomationSecurity = msoAutomationSecurityLow
    > > > MsgBox "In resetSec"
    > > > End Sub
    > > > Sub testSecurity() 'TEST WITH SECURITY DG SET TO HIGH
    > > > secAuto = Application.AutomationSecurity
    > > >
    > > > Application.AutomationSecurity = msoAutomationSecurityByUI
    > > > Application.OnTime Now() + TimeSerial(0, 0, 10), "resetSec"
    > > > Application.Dialogs(xlDialogOpen).Show
    > > > If Err.Number <> 0 Then MsgBox Err.Description
    > > > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > > > 'IF ANY FILES OPENED HAVE CODE
    > > > 'PREVENTED FROM RUNNING BY THE
    > > > 'MACRO SECURITY
    > > > MsgBox "all done"
    > > > Application.AutomationSecurity = secAuto
    > > > End Sub
    > > >
    > > > --
    > > > Regards,
    > > >
    > > > Tushar Mehta
    > > > www.tushar-mehta.com
    > > > Excel, PowerPoint, and VBA add-ins, tutorials
    > > > Custom MS Office productivity solutions
    > > >
    > > > In article

    <1111541013.796146.260020@z14g2000cwz.googlegroups.com>,
    > > > xlswalt@aol.com says...
    > > > > Hi,
    > > > >
    > > > > Starting with a copy of the sample from the help system, I

    modified
    > > it
    > > > > slightly as you can see below. I find that not only is the

    code
    > > in an
    > > > > unsigned project prevented from running (As intended), but all

    > > code,
    > > > > even in the project 'controlling' the open, is immediately

    halted.
    > > > > This prevents the reset of the AutomationSecurity level.
    > > > >
    > > > > 'MODIFIED FROM VBA HELP SAMPLE
    > > > > Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    > > > > Dim secAuto As MsoAutomationSecurity
    > > > > secAuto = Application.AutomationSecurity
    > > > >
    > > > > 'MODIFIED FROM FORCEDISABLE TO ByUI
    > > > > Application.AutomationSecurity = msoAutomationSecurityByUI
    > > > > 'MODIFIED THIS LINE
    > > > > Application.Dialogs(xlDialogOpen).Show
    > > > >
    > > > > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > > > > 'IF ANY FILES OPENED HAVE CODE
    > > > > 'PREVENTED FROM RUNNING BY THE
    > > > > 'MACRO SECURITY
    > > > > Application.AutomationSecurity = secAuto
    > > > > End Sub
    > > > >
    > > > > Does anyone know a better way to do this?
    > > > >
    > > > > Best Regards,
    > > > > Walt Weber
    > > > >
    > > > >

    > >
    > >



  10. #10
    Walt Weber
    Guest

    Re: Code Halt - prevents reset of the AutomationSecurity level

    Hi Tushar,

    Oh, I hadn't noticed the public variables being reset.
    That makes 'msoAutomationSecurityByUI' almost useless for
    my practical purposes. Otherwise, the 'OnTime' idea
    might be worth pursuing if you could decide it's OK to
    reset 'AutomationSecurity' to Excel's
    default 'msoAutomationSecurityLow'. I too tried the 'On
    Error Resume Next' - same result.

    Thank you for your effort and the better understanding.

    If it's not too much to ask, would you please look at my
    March 19th post (Different subject: 'certificates are
    being lost consistently') on
    Microsoft.Public.Office.Developer.VBA. I've had no
    response so far. Some better understanding there might
    help me avoid this issue.

    Best Regards,
    Walt Weber

    >-----Original Message-----
    >Well, I spent the better part of the last hour trying to

    make this
    >work. Tried a class with a 'withevents' application,

    tried a 'on error
    >resume next' (which the documentation at
    >http://support.microsoft.com/default.aspx?scid=kb;en-

    us;317405 implies
    >-- but doesn't really state -- should work), tried a

    modeless userform,
    >and tried a OnTime procedure.
    >
    >What seems to happen is that XL/VBA causes a fault in the

    code that
    >resets the module; however, the fault is so 'hard' that

    the on error
    >clause doesn't work (but not so hard so as to cause XL to

    crash).
    >Since all global variables are reset, you cannot save the

    current
    >setting in a global variable.
    >
    >Bottom line: The procedure specified in the OnTime method

    does get
    >executed. While I tested it with something trivial (see

    below), in
    >reality it would have to be much more sophisticated. It

    would have to
    >detect if either of the open dialog box or the security

    warning dialog
    >box is still visible. If so, it would have to reschedule

    itself for a
    >later time.
    >
    >Public secAuto As MsoAutomationSecurity
    >Sub resetSec()
    > Application.AutomationSecurity =

    msoAutomationSecurityLow
    > MsgBox "In resetSec"
    > End Sub
    >Sub testSecurity() 'TEST WITH SECURITY DG SET TO HIGH
    > secAuto = Application.AutomationSecurity
    >
    > Application.AutomationSecurity =

    msoAutomationSecurityByUI
    > Application.OnTime Now() + TimeSerial(0, 0,

    10), "resetSec"
    > Application.Dialogs(xlDialogOpen).Show
    > If Err.Number <> 0 Then MsgBox Err.Description
    > 'CODE HALTS, & NEVER GETS TO NEXT LINE
    > 'IF ANY FILES OPENED HAVE CODE
    > 'PREVENTED FROM RUNNING BY THE
    > 'MACRO SECURITY
    > MsgBox "all done"
    > Application.AutomationSecurity = secAuto
    > End Sub
    >
    >--
    >Regards,
    >
    >Tushar Mehta
    >www.tushar-mehta.com
    >Excel, PowerPoint, and VBA add-ins, tutorials
    >Custom MS Office productivity solutions
    >
    >In article

    <1111541013.796146.260020@z14g2000cwz.googlegroups.com>,
    >xlswalt@aol.com says...
    >> Hi,
    >>
    >> Starting with a copy of the sample from the help

    system, I modified it
    >> slightly as you can see below. I find that not only

    is the code in an
    >> unsigned project prevented from running (As intended),

    but all code,
    >> even in the project 'controlling' the open, is

    immediately halted.
    >> This prevents the reset of the AutomationSecurity level.
    >>
    >> 'MODIFIED FROM VBA HELP SAMPLE
    >> Sub Security() 'TEST WITH SECURITY DG SET TO HIGH
    >> Dim secAuto As MsoAutomationSecurity
    >> secAuto = Application.AutomationSecurity
    >>
    >> 'MODIFIED FROM FORCEDISABLE TO ByUI
    >> Application.AutomationSecurity =

    msoAutomationSecurityByUI
    >> 'MODIFIED THIS LINE
    >> Application.Dialogs(xlDialogOpen).Show
    >>
    >> 'CODE HALTS, & NEVER GETS TO NEXT LINE
    >> 'IF ANY FILES OPENED HAVE CODE
    >> 'PREVENTED FROM RUNNING BY THE
    >> 'MACRO SECURITY
    >> Application.AutomationSecurity = secAuto
    >> End Sub
    >>
    >> Does anyone know a better way to do this?
    >>
    >> Best Regards,
    >> Walt Weber


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1