Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is it possible to find out what Excel's security settings are with VBA. I realise, of course, that you cannot change them, but can you read them?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mark,

    Exactly which security settings are you referring to?

    Workbook Protection & Sheet Protection can be accessed and changed on the fly with VBA.

    There are also: Unprotect Sharing Method, WritePassword Property, HasPassword Property, PasswordEncryptionFileProperties Property, PasswordEncryptionProvider Property, etc. I haven't used any of these but they are there to be investigated.

    The list was from a search of the VBA 2003 Help File on Password.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, I wasn't very clear.

    I am referring to the Trust Centre settings.

    When Trust Centre doesn't allow a macro to run without user intervention (becuase the settings do not allow macros to run without a prompt), it would be nice to throw up a message, after the user has accepted the macro, to tell the user how to change their Trust Centre settings to prevent the prompt in future.

    Any ideas?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mark,

    This is modified code from the Example in the Excel 2003 VBA Help file searching on Security.

    Code:
    Option Explicit
    
    Sub Auto_Open()
    
        Dim secAutomation As MsoAutomationSecurity
        Dim zLevel        As String
        
        secAutomation = Application.AutomationSecurity
        
        
        Select Case secAutomation
              Case msoAutomationSecurityLow: zLevel = "Low"               'Level 1
              Case msoAutomationSecurityByUI: zLevel = "By UI"            'Level 2
              Case msoautomationsecurityforcedisable: zLevel = "Disabled"  'Level 3
              Case Else: zLevel = "Unknown"
        End Select
    
        MsgBox "Current Level: " & zLevel, vbOKOnly + vbInformation, _
               "Macro Security Level"
    
    ' *** Example code on changing level, do something, change level back ***
    '    Application.AutomationSecurity = msoautomationsecurityforcedisable
    '    Application.FileDialog(msoFileDialogOpen).Show
    '
    '    Application.AutomationSecurity = secAutomation
    
    End Sub    '*** Auto_Open() ***
    So you should be able to test and provide the user the desired messages via the message box function as shown. Of course, you'll be more specific!

    Update: these values are also in the Excel 2007 help file.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. This is even better than I thought was possible.
    I can actually change the setting in my macro.

    My addin loads Ok, but Trust Center settings cause security prompts when my addin loads additional workbooks. With this object I can now turn off the Trust Centre settings so that the additional workbooks load without prompts.

    Thanks for your help.

Posting Permissions

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