Microsoft Excel CommandBarControl object

[BenFinkel]

I am trying to automate an item on the commandbar "cells" in Microsoft Excel.

I am adding a new item to the menu, and I have some VB code I want it to run when the item is clicked.


If Not Application.Intersect(Target, Range("F13:F37")) Is Nothing Then
Set ctlTemp = Application.CommandBars("cell").Controls.Add(Type:=msoControlButton, before:=1, temporary:=True)
ctlTemp.Caption = "Search on Assembly #"
ctlTemp.OnAction = "EnterPart"
ctlTemp.Tag = "vlvwiz"
End If



The problem is that in Excel, the OnAction property requires a Macro, not a VB sub/function.

Does anyone know how I could make this Menu Item run my VB code?

Thanks!

[chander]

hey Ben .. did u get any solution of ur prob .. i too want to use VB function instead of macro .. but even i m not able to call a macro which in some template how i can call a macro which is in other template with OnAction property ...

[BenFinkel]

Hey..

Yea, I did figure it out. The code is at work though, and I'm home for the evening. I'll post it for you tomorrow morning (I'm EST, so thats like 8 hours from now).

--Ben


<Added>

I'm not sure I understand what it is you want to do though. Could you explain more thouroughly? I can probably help. If not, try www.Experts-Exchange.com

[chander]

No probs .. i will check it tomm.. actually i added one command bar from VB application and wanted to set its OnAction proerpty for that button.. i want to call VB function but i can get my work from some macro also but i m not getting how this macro to be set in OnAction property .. i have posted some sequential link in other thread ..

http://www.vbforums.com/showthread.php?s=&threadid=44985

check out that ...

[BenFinkel]

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)

For Each ctlTemp In Application.CommandBars("cell").Controls
If ctlTemp.Tag = "vlvwiz" Then ctlTemp.Delete
Next ctlTemp

If Not Application.Intersect(Target, Range("A13:A37")) Is Nothing Then
Set ctlTemp = Application.CommandBars("cell").Controls.Add(Type:=msoControlButton, before:=1, temporary:=True)
ctlTemp.Caption = "Open Valve Wizard"
ctlTemp.OnAction = ActiveSheet.CodeName & ".FindAssembly"
ctlTemp.Tag = "vlvwiz"
Application.CommandBars("cell").Controls(2).BeginGroup = True
End If
End Sub

Private Sub FindAssembly()

Dim vdReturn As New CValveData
Dim vlvwiz As New CValveWizard

vlvwiz.FindAssembly vdReturn, glTarget, ActiveSheet

End Sub




-Hope this helps!