Trust Access to Visual Basic Project

[Farah]

Hello,

Is it possible to do something like this through a code:

Sub Farah()
If 'Trust to Visual Basic Project' is Already Checked Then
Exit Sub
Otherwise

Application.SendKeys "%T"
Application.SendKeys "M"
Application.SendKeys "S", True
Application.SendKeys "^{PGDN}"
Application.SendKeys "{TAB}"
Application.SendKeys "{TAB}"
Application.SendKeys "{TAB}"
Application.SendKeys "{TAB}"
Application.SendKeys "{TAB}"
Application.SendKeys " ", True
Application.SendKeys "~", True

End Sub


Warm Regards,
Farah.

 

[tusharm]

AFAIK, MS does not provide access to the various security settings. Sensibly so since it would make it that much easier to write malicious code.

IMO, it is a lot more secure to ask the user to grant code the necessary access rights than writing additional code that tries to circumvent security settings.

 

[Farah]

Hello tushar,
Thank you for your reply,

Sorry, my english is too poor, did you mean that such a code can not be written to verify if the checkbox is checked already and if it is not, then follow sendkeys method otherwise End Sub?

Honestly, I am not comfortable to add an action, which might check if 'Trust Access to VB is checked and then let users understand what is it all about and make them Tick it manually.

I thought of adding friendly msgbox to go to Tools | Macro........... and make them check the box, but if they don't, its a mess for me.

Can you please help.

Warm Regards,
Farah

 

[Tom Urtis]

The user will need to establish that trusted access setting maually. That is the reason why this security feature was instituted in 2002, to give the user some control over their local system when other code attempts to write to their VBE, as Tushar said.

I wrote the following code for apps I design for some of my clients, which checks the local system (user's) trusted access status. If access has been set already, I don't bother them with any warnings. If access has not been granted, the macro will terminate, and the user will be told why (no VBE access) and how they can grant access to make the macro work for them after that. Go ahead and modify the code as you need to for your situation from this hypothetical macro that gives an example of how to check for trusted source status:


Sub CheckStatus()

MsgBox "This represents the start of your macro.", 64, "Beginning of hypothetical larger macro."

Dim MyVBE As Object
If Val(Application.Version) >= 10 Then
On Error Resume Next
Set MyVBE = ActiveWorkbook.VBProject
If Err.Number <> 0 Then
MsgBox _
"The security settings for Excel on your computer are not set" & vbCrLf & _
"to allow this code to execute. This security measure deals" & vbCrLf & _
"with permissible access to the Visual Basic Editor, and was" & vbCrLf & _
"added to versions beginning with Excel2002, which is also" & vbCrLf & _
"known as version 10, and your version is " & Val(Application.Version) & "." & vbCrLf & vbCrLf & _
"To enable your computer to accept this code, please follow" & vbCrLf & _
"these steps:" & vbCrLf & vbCrLf & _
"(1) Click the OK button at the bottom of this message." & vbCrLf & _
"(2) From the worksheet menu, click Tools > Macro > Security." & vbCrLf & _
"(3) Select the tab named ''Trusted Publishers''." & vbCrLf & _
"(4) Select by putting a checkmark in the box next to" & vbCrLf & _
" ''Trust access to Visual Basic Project''." & vbCrLf & _
"(5) Click the OK button to exit the Security dialog." & vbCrLf & vbCrLf & _
"After that, come back here and try running the macro again.", _
16, "Cannot continue due to security settings - - see explanation below:"
Exit Sub
End If
End If

MsgBox _
"If you see this, then VBE access" & vbCrLf & _
"has been granted by the user.", , "The rest of your code goes here !"

End Sub

 

[Farah]

If access has not been granted, the macro will terminate, and the user will be told why (no VBE access) and how they can grant access to make the macro work for them after that.

Hi Tom,

I tested your above code, works great, Thank you...

Is it possible that a text saying "Print this dialogue" or a commandbutton added to the message; so that if a user want to print the instructions to easily carry them out.

Thank you Once again

Warm Regards,
Farah

 

[Tom Urtis]

You can adjust the code for the MessageBox to be a Yes / No question instead of an OK statement, the question at the end being, "Do you want to print these instructions?". If No exit the Sub, if Yes print the sheet with the instructions on it that you would create and hide ahead of time. I suppose you could write code to take a picture of the message box and print that but simply printing pre-fab text instructions is easiest. Lots of examples on the board for question messageboxes, then just record a macro to unhide the sheet, print it, and re-hide as code for the Yes condition in MsgBox question. Another option if you don't want an extra sheet in the workbook for that purpose is to programmatically create one with Sheets.Add, then populate the first few cells with the steps, print the sheet and delete the sheet.