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(msoControlBu tton, , , , 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
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(msoControlBu
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant, just what I needed to know.
I am happy with either syntax.
Thanks also for the FaceID information.
I am happy with either syntax.
Thanks also for the FaceID information.
Glad to help
Chris
Chris
You need to set the parameter in teh button definition and access it in the called sub ... see the following example
Regards,
chris_bottomley
Open in new window