Solved: Can you set Security through VBA?

[SherryO]

I want to check the trusted sources check boxes for my users. Is there anyway to do this via VBA? Thanks!!!

[lucas]

That would kinda defeat the purpose of it wouldn't it? I don't think you can.

[SherryO]

Massive cringing. Yes, that's what I thought before I posted, but I figured it was worth the effort. Thanks!

[lucas]

If you could do that then you could write code to anyones workbook you had access to.....!!! Could be deadly

[SherryO]

I agree, I just know I'll have 25 people calling me wondering why x wouldn't work, when the don't read the instructions:> Thanks again.

[Ivan F Moala]

Have a look here

http://www.xcelfiles.com/Security.html

see if that helps any ??

[johnske]

Or something like this may help remind them...

Private Sub Workbook_Open()
Call AddRefsIfAccessAllowed
End Sub


Private Sub AddRefsIfAccessAllowed()

Dim Response As VbMsgBoxResult

'Test to ensure access is allowed
If Application.Version > 9 Then
Dim VisualBasicProject As Object
On Error Resume Next
Set VisualBasicProject = ActiveWorkbook.VBproject
If Not Err.Number = 0 Then
Response = Msgbox("Your current security settings do not allow the code in this workbook " & vbNewLine & _
" to work as designed and you will get some error messages." & vbNewLine & vbNewLine & _
"To allow the code to function correctly and without errors you need" & vbNewLine & _
" to change your security setting as follows:" & vbNewLine & vbNewLine & _
" 1. Select Tools - Macro - Security to show the security dialog" & vbNewLine & _
" 2. Click the 'Trusted Sources' tab" & vbNewLine & _
" 3. Place a checkmark next to 'Trust Access to Visual Basic Project'" & vbNewLine & _
" 4. Save - then Close and re-open the workbook" & vbNewLine & vbNewLine & _
"Do you want the security dialog shown now?", vbYesNoCancel + vbCritical)
If Response = vbYes Then Application.CommandBars("Macro").Controls("Security...").Execute
Exit Sub
End If
End If

'Call AddReference

End Sub

[SherryO]

Thank you to both of you. I think this would be perfect to incorporate. I appreciate it!! Sherry

[SherryO]

Ivan - I get a compile error expected function or variable on Execute after send keys. Any suggestions? thanks

[lucas]

I ran this just fine...

Sherry...open a blank Excel and go to security and uncheck
On the trusted publishers tab: Trust access to visual basic project
make sure it's unchecked and close excel....

Now open the file I attached to this post and click the button...

[SherryO]

I did and it worked fine. When I stepped through it the send keys sent a 't' into the vbe. I didn't catch it for a couple of tries and until I realized that it didn't match the orginal code. I did have a question for you if you don't mind. How would one go about running the same kind of thing for MSProject? This works so well, it would be a shame not to do something similar with MSProject as my install includes info to both applications. Thanks for your patience!

[lucas]

I would think that this could be put in the thisworkbook module and run from document open too....you could put the function in a standard module I think...you would have to experiment.

As far as msProject.....I just don't know as I don't have project...you would have to make sure that SecuritySet was available in project and you would have to figure out how to define the project instead of thisworkbook as in the opening line:
If Not SecuritySet(ThisWorkbook) Then