Microsoft Excel CommandBarControl object

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.

Code:

---

    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!

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 ...

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

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.p...threadid=44985

check out that ...

Code:

---



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!