?
Solved

Using OnAction in Word VBA toolbar controls

Posted on 2009-12-16
4
Medium Priority
?
1,616 Views
Last Modified: 2013-12-20
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
0
Comment
Question by:LostInESpace
  • 3
4 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 26061832
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

0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 26061878
chris_bottomley,

WHilst I tested my code since I was familiar with the syntax, hopefully the following equivalent in your code will work

chris_bottomley
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"  
            .Parameter = .Caption
        End With
    End With
End Sub 
Public Sub MyTestMacro()
    MsgBox "The name of the menu clicked was " & Application.CommandBars.ActionControl.Parameter
End Sub

Open in new window

0
 

Author Closing Comment

by:LostInESpace
ID: 31666713
Brilliant, just what I needed to know.
I am happy with either syntax.
Thanks also for the FaceID information.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 26062186
Glad to help

Chris
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question