Regarding Menu buttons

[Prathap Adusumilli]

Hi all,

I am having the following problem regarding invoking an application
from an user created menu item.

I have added a button in the "Tools" menu and i am trying to ionvoke an
..exe file from that button and i have written the following code.

Set App = Application
'Set cbar1 = App.CommandBars("Tools").Controls.Add("Custom1",
msoBarFloating)


Set cmdBar = Application.CommandBars("Tools")
' if the button already exists then delete it
For Each cmdBarMenuItem In cmdBar.Controls
If cmdBarMenuItem.Caption = "&Make Policy" Then
cmdBarMenuItem.Delete
'If cmdBarMenuItem.Caption = "Make Policy" Then
cmdBarMenuItem.Delete
Next cmdBarMenuItem
Set newItem = CommandBars("Tools").Controls.Add(Type:=msoControlButton)
With newItem
.BeginGroup = True
.Caption = "&Make Policy"
.FaceId = 0
'MsgBox ("Welcome Prathap")
.OnAction = "!<MyAddIn.MySub>"

End With

Sub MySub()
MsgBox ("Welcome Prathap")
Shell ("C:\Policy.exe")
End Sub

But neither the message box nor the .exe file is getting executed. Can
any one please help me about.

 

[Bob Phillips]

Prathap,

Sub createmenu()
Dim cmdbar As CommandBar
Dim CBCtl As CommandBarControl
Dim NewItem As CommandBarControl

'Set App = Application
'Set cbar1 =
App.CommandBars("Tools").Controls.Add("Custom1",msoBarFloating)

Set cmdbar = Application.CommandBars("Worksheet Menu Bar")
Set CBCtl = cmdbar.Controls("Tools")
' if the button already exists then delete it
On Error Resume Next
CBCtl.Controls("&Make Policy").Delete
On Error GoTo 0

Set NewItem = CBCtl.Controls.Add(Type:=msoControlButton,
temporary:=True)
With NewItem
.BeginGroup = True
.Caption = "&Make Policy"
.FaceId = 0
.OnAction = "MySub"
End With
End Sub

Sub MySub()
MsgBox ("Welcome Prathap")
Shell ("C:\Policy.exe")
End Sub

If you have .Net, note the change to the OnAction.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)