以编程方式显示Excel的“信任中心设置"对话 [英] Programatically bringing up Excel's "Trust Center Settings" dialog

问题描述

我需要用户单击对VBA项目对象模型的信任访问"，以便我正在构建的应用程序可以帮助他们将代码导入VBE.

I need users to click the "Trust access to the VBA project object model" so that an app I'm building can help them import code to the VBE.

我可以显示一个消息框或提供文档，告诉他们如何更改设置(如果这样做，还会警告他人).但是，该设置仍然被隐藏在Excel UI中，只需单击5次……实际上，即使是我也很难记住在哪里可以找到它.

I could display a messagebox or provide documentation telling them how to change setting (and warning about repercussions if they do). But that setting is still buried 5 clicks down in the Excel UI...in fact even I have trouble remembering where to find it.

所以我想做的就是以编程方式为他们打开该窗口.

So what I'd like to do is programmatically open that window for them.

我可能可以使用臭名昭著的SendKeys方法，但是我想知道是否有更好的方法...

I could probably use the notoriously fickle SendKeys method, but I wonder if there is a better way...

(我看到，当目标Office应用程序未按以下方式运行用户时，实际上可以使用VBS脚本通过使用VBS脚本来更改键的值，从而通过宏对用户进行设置的访问.每个

(I see that you can actually set access to the VBA project object model for users via a macro by using a VBS script to change the key's value when the target Office application is not running them as per this MSDN article but I would much prefer that the user manually enables this option or elects not to).

VBA 或 VSTO解决方案很好，或者您可以想到的其他任何东西.

VBA or VSTO solutions are fine, or anything else you can think of.

推荐答案

这将弹出对话框.

Application.CommandBars.ExecuteMso("MacroSecurity")

或者，这也做同样的事情:

Alternatively, this does the same thing:

Application.CommandBars.FindControl(Id:=3627).Execute

用户将需要选中该框，即使使用SendKeys也无法通过编程方式对其进行检查.

The user will need to check the box, it cannot be checked programmatically even using SendKeys.

额外信用:可以更改注册表项吗?

还有一个注册表项，您可以使用VBA对其进行更改.

There is also a registry key which you may be able to change using VBA.

我认为您也许可以使用这样的子例程来将注册表项设置为允许访问".但是，正如我对此进行测试并确实更改了注册表项的值一样，它似乎对我访问VBOM的能力没有任何影响:

I thought you might be able to use a subroutine like this to set the registry key to "allow access". However, as I tested this and it does change the registry key value, it doesn't seem to have any effect on my ability to access the VBOM:

• 如果我的设置不允许访问，并且将键值更改为1，则会收到1004错误.
• 如果我的设置 do 允许访问，并且我将键值更改为0，则操纵VBOM的示例代码仍然有效.
• 如果我以编程方式更改了注册表项，则在重新启动Excel Application时它将恢复为以前的状态.

• If my settings do not allow access, and I change the key value to 1, then I get a 1004 error.
• If my settings do allow access, and I change the key value to 0, sample code that manipulates the VBOM still works.
• If I change the registry key programmatically, it reverts to its previous state when re-starting the Excel Application.

我可能做错了什么，所以我将其留在这里，以免其他人可以使它起作用.我已经使用这种功能为自己的应用程序设置自定义注册表项，即存储应用程序的当前版本号等，但是也许注册表的这一部分只是被锁定了，因此无法通过这种方式进行操作. /p>

It's possible I did something wrong, so I will leave this here on the off-chance that someone else can get it to work. I have used this sort of function to set custom registry keys for my own applications, i.e., to store the application's current version #, etc. but perhaps this part of the registry is simply locked down and can't be manipulated this way.

Const regKey As String = "HKEY_CURRENT_USER\SOFTWARE\MICROSOFT\OFFICE\14.0\Excel\Security\AccessVBOM"
Sub AllowAccessToVBOM()
        With CreateObject("WScript.Shell")
            'write registry key
            .RegWrite regKey, "0", "REG_DWORD"
            MsgBox regKey & " : " & .regRead(regKey)
        End With


End Sub

这篇关于以编程方式显示Excel的“信任中心设置"对话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！