VBA that will add workbook to Trusted Location list
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
VBA that will add workbook to Trusted Location list
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA that will add workbook to Trusted Location list
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.
- 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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: VBA that will add workbook to Trusted Location list
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.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA that will add workbook to Trusted Location list
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.
It won't hurt if the user runs the macro twice and answers Yes both times.
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
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: VBA that will add workbook to Trusted Location list
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.
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.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA that will add workbook to Trusted Location list
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: VBA that will add workbook to Trusted Location list
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
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.
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
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA that will add workbook to Trusted Location list
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)
(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
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: VBA that will add workbook to Trusted Location list
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.
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
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
I like it very much, thank you
I just wanted to make as easy as possible for an inexperience user
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: VBA that will add workbook to Trusted Location list
Lets say I want to remove the yes /no option using this code
would this be the correct way
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: VBA that will add workbook to Trusted Location list
Thank you Hans