Office 2007 -- Display Macro Security Dialog from the Trust Center?

[Messenger]

Hey All,

Is there a way to display the Trust Center dialog for Macro Settings via
VBA? For example, historically some of the apps, such as Word or Excel, for
example have a list of built-in dialogs one can show.

Is there a way to display the Macro Settings dialog?

 

[Andy Pope]

Hi,

Try,

application.CommandBars.ExecuteMso "MacroSecurity"


Cheers
Andy

 

[Messenger]

Thanks Andy. Appears to be just what I needed ;-)

By the way, I noticed that "MacroRecordOrStop" and "MacroRecord" do not
seem to work. Any thoughts on getting the "Record Macro" dialog to display?

 

[Andy Pope]

Hi,

Macro record works,

Application.CommandBars.ExecuteMso "MacroRecord"

When the macro record button is pressed it automatically becomes the
stop button. So I'm not sure how you will be able to stop it would a
button being pressed.

Cheers
Andy

 

[Messenger]

Hmmm.... I'm getting a "Method 'ExecuteMso' of object '_CommandBars' failed"
error with that.

I'm only trying to display the Record Macro dialog

 

[Tony Jollans]

You don't say why you want the dialog, but you shouldn't be able to record a
macro when you are running a macro, so it is no surprise to me that it
fails.

 

[Messenger]

Uhhh... not sure why stating why I want the dialog is so important or a
mystery. Ever want to put some standard functionality on a custom menu?
Jeesh... it still seems odd to me that they offer it as an option within the
app dialogs but there is no way to use that option.

Considering that the macro recorder adds material to a module in VBA and
considering that one can potentially create code which adds material to a
module in VBA, to me, it is surprising that it fails.

 

[Messenger]

The dialog can be displayed via automation apparently.

 

[Tony Jollans]

Well, pardon me for speaking! I cannot imagine any use of the Record Macro
dialog other than as a precursor to recording a macro. I just thought that
if you said what you were trying to do, somebody might know another way.

I could be wrong about being unable to record (I admit I have nothing to
back it up with) - and you make a valid point. FWIW, I can display the
dialog using "MacroRecord" and if I press OK it indicates that it is
recording but (a) doesn't actually record anything and (b) won't let me stop
'recording'.

 

[Messenger]

Hey Tony,

Apologies if I misunderstood the tone of your previous post.

Anyway, just wanted to offer something on a custom menu is all.

Interesting that you can get it to work with "MacroRecord" as it fails on my
system. Are you using something like the following:

Application.CommandBars.ExecuteMso "MacroRecord"

 

[Tony Jollans]

Are you using something like the following:

Application.CommandBars.ExecuteMso "MacroRecord"

Exactly that - and I just tried it on 2010 on a different machine and it
does the same there as well. I have no idea what may cause it not to work
for you, sorry.

 

[Andy Pope]

Hi,

I created this ribbonx xml for a workbook. It add a group to the Home
Tab with a Record macro button. When pressed the macro dialog is
displayed. The code is recorded. The button automatically changes to the
Stop macro record button.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
<ribbon >
<tabs >
<tab idMso="TabHome" >
<group
id="Group1"
label="Group1">
<button idMso="MacroRecord" label="My Macro Recorder"/>
</group >
</tab >
</tabs >
</ribbon >
</customUI >

Cheers
Andy

 

[Messenger]

Hey Andy,

Thanks. I am familiar with the idMso. I was hoping to do something custom
but, so it appears, this will not be possible ;-)

Thanks for the help.