VBA that will add workbook to Trusted Location list

[ABabeNChrist]

I’m just thinking out loud, is there a VBA approach that will add opened workbook to Trusted Location List by using a yes and no option and a Msg

[HansV]

In theory, it is possible to add a trusted location by creating a registry key, but I don't think it's a good idea:
- If the workbook is not already in a trusted location, macros will most probably be disabled, so the code that you propose wouldn't run.
- The keys are different for different versions of Excel.

[ABabeNChrist]

The reason why I was asking was that I have experienced on a couple different occasions where the user who was very limited with computer skills had a hard time with setting up a trusted location and I was just trying to figure out the most simplest approach and help prevent user from having to Enable this content each and every time workbook is opened. I don’t really want to have them set all macros to enable.

[HansV]

The following macro will work in Excel 2007, it won't do anything useful in earlier or later versions of Excel.
The user must have enabled macros, otherwise it won't run, obviously.

Code: Select all

Sub AddPathToTrustedLocations()
  Const strMsg = "Do you want to add the path of this workbook " & _
    "to your Trusted Locations?"
  Const strReg = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & _
    "12.0\Excel\Security\Trusted Locations\ABabeNChrist\"
  Dim objShell As Object
  Dim strPath As String
  If MsgBox(strMsg, vbQuestion + vbYesNo) = vbYes Then
    Set objShell = CreateObject("WScript.Shell")
    strPath = ActiveWorkbook.Path
    objShell.RegWrite strReg & "Path", strPath, "REG_SZ"
    Set objShell = Nothing
  End If
End Sub

It won't hurt if the user runs the macro twice and answers Yes both times.

[ABabeNChrist]

WOW Hans
That is truly amazing it works great.
I have a question regarding a certain line of code that has “ABabeNChrist”
What purpose does this serve? I understand it refers to a location, but I only use this name as my screen name.
Thank you so very much for your time
I will now try and figure the best way to implement this approach.

[HansV]

Each trusted location is a registry key which must have a unique name. The standard names are Location0, Location1 etc., but we don't know how many a particular user will have, so we can't just add a new key named (for example) Location10. So the idea is to give the new key a name which we can be sure is not in use yet. I used your Lounge username, but you may use anything which is guaranteed to be unique instead of that - you could use your real last name, or the name of your company, or just a fantasy name.

[ABabeNChrist]

Hi Hans
That was very interesting and very well written.
I thank you so much for the code.
I was wondering is it possible that if workbook was already listed on the Trusted locations that the yes and no option would not open up, Then using may be an “Else” that would open UserForm. The reason why is when this workbook may be introduced for the first time on a new computer where the macros have not been set (which seems to be almost every time). When the workbook is first opened up it would normally open to a UserForm but because the macros are not set the workbook would open to Sheet1. I use Sheet1 as a dummy sheet because all working sheets are hidden. At this point just below the tool ribbon is the macro security warning

Macro settings.JPG

I would then instruct the user to select “enable this content” then select button to start program.
This start button would then have this line of code that is mentioned in this thread. So after going through this process once the user will not have to experience this again.

[HansV]

This version will check whether the registry value already exists, and if so, whether it contains the path of the workbook. If it's OK, nothing will happen, otherwise it'll ask the user for permission to add the registry value.
(If the user has added the registry value, then moved the workbook to another folder which is not a trusted location, the macro will change the existing registry value to match the new path)

Code: Select all

Sub AddPathToTrustedLocations()
  Const strMsg = "Do you want to add the path of this workbook " & _
    "to your Trusted Locations?"
  Const strReg = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & _
    "12.0\Excel\Security\Trusted Locations\ABabeNChrist\"
  Dim objShell As Object
  Dim strValue As String
  Dim strPath As String
  Set objShell = CreateObject("WScript.Shell")
  strPath = ActiveWorkbook.Path
  On Error Resume Next
  strValue = objShell.RegRead(strReg & "Path")
  On Error GoTo 0
  If strValue <> strPath Then
    If MsgBox(strMsg, vbQuestion + vbYesNo) = vbYes Then
      objShell.RegWrite strReg & "Path", strPath, "REG_SZ"
    End If
  End If
  Set objShell = Nothing
End Sub

[ABabeNChrist]

Hi Hans
I added a couple additional lines of code at the end. That will control the UserForms and it seems to work fantastic
As I mentioned earlier in the thread this workbook opens to a UserForm (UserForm1), but of course if the macros are not enabled the UserForm will not open. So I attached the code to UserForm1 So that when the user click on the security warning options to “Enable this content” UserForm1 would then open up, then the user would select start report that would then run this line of code.

Code: Select all

    Const strMsg = "Do you want to add the path of HomInspect " & _
          "to your Trusted Locations?"
    Const strReg = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & _
          "12.0\Excel\Security\Trusted Locations\ABabeNChrist\"
    Dim objShell As Object
    Dim strValue As String
    Dim strPath As String
    Set objShell = CreateObject("WScript.Shell")
    strPath = ActiveWorkbook.Path
    On Error Resume Next
    strValue = objShell.RegRead(strReg & "Path")
    On Error GoTo 0
    If strValue <> strPath Then
        If MsgBox(strMsg, vbQuestion + vbYesNo) = vbYes Then
            objShell.RegWrite strReg & "Path", strPath, "REG_SZ"
        End If
    End If
    Set objShell = Nothing
    UserForm2.Show
    Unload UserForm1

So the next time this workbook is opened, the macros will be enabled and going from UserForm1 to UserForm2, will be with no message
I like it very much, thank you
I just wanted to make as easy as possible for an inexperience user

[ABabeNChrist]

Lets say I want to remove the yes /no option using this code
would this be the correct way

Code: Select all

Private Sub CommandButton1_Click()
    Const strReg = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & _
          "12.0\Excel\Security\Trusted Locations\ABabeNChrist\"
    Dim objShell As Object
    Dim strValue As String
    Dim strPath As String
    Set objShell = CreateObject("WScript.Shell")
    strPath = ActiveWorkbook.Path
    On Error Resume Next
    strValue = objShell.RegRead(strReg & "Path")
    On Error GoTo 0
    If strValue <> strPath Then
            objShell.RegWrite strReg & "Path", strPath, "REG_SZ"
    End If
    Set objShell = Nothing
    UserForm2.Show
    Unload UserForm1
End Sub

[HansV]

Yes, that looks correct.

[ABabeNChrist]

Thank you Hans