Link to home
Start Free TrialLog in
Avatar of LostInESpace
LostInESpace

asked on

Using OnAction in Word VBA toolbar controls

I am trying to create a menu control on the fly which, when actioned, runs a macro. I need to pass an argument to the macro which is the caption of the control that was clicked

I am using:
.OnAction = "'MyTestMacro """ & .Caption & """'"  

The menu item is created OK but when I then click on it to run MyTestMacro it comes up with a error message
"The macro cannot be found or has been disabled because of your Macro security settings"

Macro Security is set to Low. Security is a red herring, I think, it is probably an issue with the syntax of the .OnAction parameter which prevents Word VBA finding the macro.

If I remove the caption argument and call the macro with no arguments then it works OK and the Message Box appears but of course the caption name is blank.
e.g.   .OnAction = "MyTestMacro"

I am assuming that this is a syntax issue but I have hunted round the web and all the examples I can find are the same as the one I have used.  

Can anyone put me right please?

This is my code:-


Sub Test()
   Dim cbrBar              As CommandBar
   Dim ctrl                As CommandBarControl
   
   On Error Resume Next
   Set cbrBar = CommandBars("Custom Popup 68578859")
   If Err <> 0 Then
      Set cbrBar = CommandBars.Add("Custom Popup 68578859")
      Err = 0
   End If
   
   With cbrBar
        Set ctrl = .Controls.Add(msoControlButton, , , , True)
        With ctrl
            .BeginGroup = False
            .Caption = "New MenuItem"
            .FaceId = 44
            .Style = msoButtonIconAndCaption
            .Tag = "TESTTAG"
            .OnAction = "'MyTestMacro """ & .Caption & """'"  
        End With
    End With
End Sub

Public Sub MyTestMacro(Optional MsgBoxCaption As String)
    MsgBox "The name of the menu clicked was " & MsgBoxCaption
End Sub
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Hello LostInESpace,

You need to set the parameter in teh button definition and access it in the called sub ... see the following example

Regards,

chris_bottomley
Sub addbutton()
Dim objbutton As Object 
    With Application.CommandBars(1)
            Set objbutton = .Controls.Add(msoControlButton)
            With objbutton
                .Style = msoButtonIconAndCaption
                'Enter your caption text'
                .Caption = "CB Test"
                .Parameter = "Fred"
                'List of face IDs here: http://www.kebabshopblues.co.uk/2007/01/04/visual-studio-2005-tools-for-office-commandbarbutton-faceid-property/'
                .FaceId = 355
                'Enter the macro you want to run.  Be sure to include the Project and module names.'
                .OnAction = "ButtonPressed"
            End With
        End With 
End Sub 
Sub buttonpressed()
MsgBox "button Pressed ... " & Application.CommandBars.ActionControl.Parameter
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LostInESpace
LostInESpace

ASKER

Brilliant, just what I needed to know.
I am happy with either syntax.
Thanks also for the FaceID information.
Glad to help

Chris