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

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

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

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

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

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 .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

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 .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

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 .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 .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

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 .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
.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