Amateur VBA developer seeks further assistance...

[colofnature]

My company (the one I work for, I don't own it or anything) is about to upgrade - finally - from WinNT to XP. They're also changing company protocol to include a rule that the secuirity level in end user products (i.e, MS Office) should be set to High.

My problem is that I have several models (spreadsheets, used as front-end to Access databases) which require lower security settings. I've trained the users in how to reduce the security settings, but I can't trust them enough to reset it after they're finished (it being my responsibility to ensure that they do). My question then is this: Can I detect, at application level, when someone opens a file whose name is in a (write protected) text file, and reduce the macro security level accordingly, and raise it again when said file is closed or deactivated.

Any ideas gratefully recieved...
Col

[Jake Marx]

Hi Col,

You may want to look into signing your VBA-enabled workbooks:

http://msdn.microsoft.com/library/de.../odc_dsvba.asp

This will allow users to "trust" your certificate _and_ leave their macro
security settings on High.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

colofnature wrote:
> My company (the one I work for, I don't own it or anything) is about
> to upgrade - finally - from WinNT to XP. They're also changing company
> protocol to include a rule that the secuirity level in end user
> products (i.e, MS Office) should be set to High.
>
> My problem is that I have several models (spreadsheets, used as
> front-end to Access databases) which require lower security settings.
> I've trained the users in how to reduce the security settings, but I
> can't trust them enough to reset it after they're finished (it being
> my responsibility to ensure that they do). My question then is this:
> Can I detect, at application level, when someone opens a file whose
> name is in a (write protected) text file, and reduce the macro
> security level accordingly, and raise it again when said file is
> closed or deactivated.
>
> Any ideas gratefully recieved...
> Col

[colofnature]

Hi Jake, thanks for the speedy reply. I don't suppose you could summarise? I was only told of the problem today and I need an answer by 10 am zulu, 16/8/6...

[Jake Marx]

Hi,

colofnature wrote:
> Hi Jake, thanks for the speedy reply. I don't suppose you could
> summarise? I was only told of the problem today and I need an answer
> by 10 am zulu, 16/8/6...

You would want to obtain a code signing certificate from a CA (certificate
authority). Here's one place to do it (notice the VBA choice):

https://www.thawte.com/process/retail/new_devel

Once you have that, you can digitally "sign" VBA projects (Tools | Digital
Signature in the VBE). When a workbook containing signed code is opened,
the user will be given the option to trust that publisher. If he/she does
so, all workbooks signed with your organization's code-signing certificate
will be trusted, and the workbook will open silently without warnings. All
other VBA code will be blocked without warning (if security is set to High).

Another option is to put all VBA code into add-ins, which could then be
installed on each user's machine. Typically, add-ins are trusted (via the
checkbox in the macro security dialog), which means the user won't be
prompted when starting Excel and code will run. However, this would
probably mean rewriting a lot of your code to use add-ins and templates,
which may not work for you.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

[colofnature]

And if I'm persona non grata with my employer, is there any way of convincing them I'm harmless? (i.ie. proving I'm not using my limited skills for dark side purposes....)

[NickHK]

If the signed macro code is Signed and Trusted, then it is ehh.. trusted.
You are specifically saying that you not believe any code using that
certificate is nefarious.
It's one way or the other; Trust it and allow it to run, whatever it does or
do not Trust that certificate so no code runs.

NickHK

"colofnature" <[email protected]>
wrote in message
news:[email protected]...
>
> And if I'm persona non grata with my employer, is there any way of
> convincing them I'm harmless? (i.ie. proving I'm not using my limited
> skills for dark side purposes....)
>
>
> --
> colofnature
> ------------------------------------------------------------------------
> colofnature's Profile:
http://www.excelforum.com/member.php...o&userid=34356
> View this thread: http://www.excelforum.com/showthread...hreadid=571978
>