Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Stevenage, Bedfordshire, England
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Returning List of Trusted Sources (VB6/Word2002)

    I have a VB application which opens a number of documents in Word 2002 with a particular template attached to them. This template has a series of macros that reads through the document making changes as it goes, then saves and closes it before moving onto the next. I have a digital certificate so that users will be able to trust my code. Unfortunately , if they haven't chosen to add my certificate to their list of trusted sources, they get a series of dialogs prompting them to enable/ disable macros. Of course, if they chose not to my application can't complete properly.
    It would be far easier if l could find out before running any macros whether l was listed as a trusted source. To do this I need to return a list of trusted sources and check through it , but l haven't got a clue where this would be held, or how to access it. If anyone has an answer (or code!) that would be great.

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    I solve this problem another way in my Excel applications. Since I require macros to be enabled and, as you said, the application doesn't work right if the user doesn't enable macros, I devised a plan so that my app doesn't work at all if the user doesn't enable macros. I have two modes, one that is visible when macros are disabled and the other when macros are enabled. <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    I have a sheet called Intro which contains text in big letters that macros must be enabled (and a few other statements applicable to the program plus a logo and contact info). When my Excel program is first run, only this Intro sheet is visible (all the other sheets are hidden with the xlVeryHidden attribute). That way if the user opens my file (which are all Excel apps) w/o macos enabled,. If macros were not enabled, all they see is this Intro sheet with the warning on it that they need to close the file and re-open w/macros enabled.

    If macros were enabled when they then open it, the workbook_open code hides the Intro sheet and makes visible all the other sheets and then program now runs as designed.

    At workbook close, I force all the sheets to be xlVeryHidden and then make the Intro sheet visible and then save it. That way I'm preparing the program for the next time it's opened just in case the user doesn't neable macros. This works because Excel starts up in the same mode it was left in so since the Intro sheet was visible and all others hidden that's all they will see.

    I don't know what app you're writing this in (Word, Excel) but if it's Excel, you should use the xlVeryHidden not just xlHidden since xlHidden sheets are visible in the Format/Sheets menu.

    The only downside is that the user has no choice when at workbook close, the code saves the file even if they didn't change anything. This is required because I have to restore the workbook to the 'non-macro' state in case it's opened again w/o macros enabled. On very large files, this can be a bit of a nuisance to the user but I'd rather do that than have them able to partially run a program and complain about errors.

    HTH, Deb <img src=/S/chatter.gif border=0 alt=chatter width=38 height=16>

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Stevenage, Bedfordshire, England
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Hi,
    Thanks for your reply. I can see how this would work in Excel, but l don't think it would work for Word, because as soon as you try to open a Word document containing macros, if you have macro security set to high or medium, you immediately get a dialog asking whether you want to enable macros - in Excel it appears that the macros just won't run, which would be great if it worked this way in Word.
    This would be fine if l could trap whether the user checked the 'Always trust macros from this source' checkbox in this dialog, otherwise just clicking 'Enable Macros' only applies to the current Word document, and the dialog will appear again next time a Word document is opened, bringing me back to square one.
    I'm starting to wonder whether l need to open Word at all to check for Trusted Sources. If l could establish that my certificate hadn't been trusted, l could just tell the user to go away and come back when they'd set things up correctly.

  4. #4
    New Lounger
    Join Date
    Oct 2002
    Location
    Stevenage, Bedfordshire, England
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    I ended up doing this workaround:
    1. Create a Word document with a simple macro function which simply returns true.
    2. From Visual basic, open the document minimised
    3. Call the Word function - if it errors, it's because the macro hasn't run properly
    4. Create an 'On Error Goto' which the program will drop through to if the macro doesn't run correctly. This error just informs the user that they haven't setup their Word security properly and directs them back to the setup documentation before exitting.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Trusted sources are stored in the registry, and can be found at

    <big>HKEY_CURRENT_USERSoftwareMicrosoftVBATruste d[/b]

    If you know the Name of the certificate you could use something like the following to test if the signaturre is trusted on the current system :
    <pre>Function IsTrusted(strSign As String) As Boolean
    Dim WSHShell, strRegKey, DigitalSign()
    Set WSHShell = CreateObject("WScript.Shell")
    IsTrusted = False
    strRegKey = "HKCUSoftwareMicrosoftVBATrusted" & strSign
    On Error GoTo Failed
    DigitalSign() = WSHShell.RegRead(strRegKey)
    IsTrusted = True
    On Error GoTo 0
    Exit Function
    Failed:
    IsTrusted = False
    End Function</pre>

    If your digital signature was named ClareTMS, you could then use <pre> If IsTrusted(ClareTMS) Then ....</pre>

    Just be careful that ther is only one digital signature with the same name, otherwise a more convoluted approach would be needed.

    Andrew C

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    Stevenage, Bedfordshire, England
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Hi, Thanks for the code, l knew it had to be held somewhere.

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Actually, it does work the same in Word, I just tested it myself. My 'solution' doesn't prevent the macro en/disable dialog from appearing. That dialog always shows up but what it does is display a sheet (or document page) that has a message on it that they can't run the program w/o macros enabled. When they re-open the file with macros, the program runs as needed.

    I see you another poster provided code for your ideal solution so that's great. I might use it too since I sometimes use certificates as well and people tend to ignore or not see the little check box to 'always accept' the certificate.

    Deb <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Great, I like this, I'll use it too. <img src=/S/cool.gif border=0 alt=cool width=15 height=15> <img src=/S/cool.gif border=0 alt=cool width=15 height=15> Got some questions...

    1. Is the 'WScript.Shell' object something that's always installed with Office or it it an extra? I have to always use the bare minimum (least common denominator) for all my projects as far as what I require the end user to have on their system. I don't want to have to force them to
    load certain references for example.

    2. Is this registry entry the same for all Windows versions? I see this entry in my WIn2k registry (since I do have an available cert) and was wondering if the VBA entry is only there if they had installed something other than the 'standard' default setup for Office.

    Thnx, Deb <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21>

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Me again.... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    I tried this code and it worked great when I had an active certificate. I then deleted that certificate and the registry entry was deleted as expected. I then re-ran the function and it still returned True for the old certificate name. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

    Do you know why that would be?

    Deb <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Deb,

    I'm not really sure but you may need to reboot or at least close and restart all Office applications. I have noticed in the past that when you create a new digital signature, it does not immediately show in the registry, suggesting a reboot might be required.

    Andrew

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Deb,

    WScript.Shell is part of the Windows Scripting Host, which is included with all recent versions of Windows and/or IE versions 5 and later. It is freely downloadable from Mocrosoft if required. If WSH is not availaable, API calls could be used to interogate the registry.

    Andrew

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning List of Trusted Sources (VB6/Word2002)

    Note that you may find a lot of shops will not allow use of the WSH due to reported security holes.

Posting Permissions

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