run a custom menu item with a commandbutton

[gjovist]

Hello all, I am a first-time poster, and first-time VB user.
That being said, here is my situation:

I am trying to create a commandbutton on my worksheet that runs a menu item called "Update UG Part". This is in an embedded spreadsheet (through Unigraphics NX2) and the command has been added to the "Tools" menu.
I’ve been searching the forums (lots of great info, just haven’t found what I need) playing with the .FindControl but can’t get it to work.
Can somebody help me out with this?

I’m using Excel2003 in Windows XP Pro and the “about VB” says VB 6.3 at the top and VBA: Retail 6.4.9972 at the bottom if that means anything to you (like I said, I’ve never seen this stuff before)
Anyway, thanks for any help you can give me. You guys have a great site and so far I’ve been able to answer any of my questions by just browsing and searching.

[Ecniv]

I was going to say can you not copy it - but a quick check says no.

So you will need to go to the vb editor (Tools>Macros>VB editor)
Add in a reference to Office 11 objects (Menubar>References and scroll down. tick the box. Click OK)
Now, assuming that the menu doesn't change you should be able to use the

commandbars("menu name").controls("controlname")

or a variant.
You will need to read up on a tutorial online or help files to use the commandbars, but I think the above should work... may be. Have a go and see what happens.

Post up if it works or not plus any code you have.

[gjovist]

Thanks for the quick reply.
I've been messing with VB for a few weeks so I know a little about the programming now.
I tried your suggestion using the "Find" command as an example:

Private Sub CommandButton3_Click()
CommandBars("Edit").Controls ("Find")
End Sub

But it gives me an "Invalid Use of Property" error on the .Controls

I have also tried this to search for the "Find" the command:

Private Sub CommandButton3_Click()
    Set lbl = MenuBars.FindControl(Type:=msoCommandFind)
    If lbl Is Nothing Then
        MsgBox "Item not found"
    Else
        MsgBox "Control" & lbl.Index & " on command bar " & lbl.Parent.Name & " matches description"
    End If
End Sub

but it won't return anything (although I could be entering the "Type:=" name wrong)
I have been searching through tutorials and help files for anything remotely related to the concept, but I've only found coding for known paths like the .Save and .SaveAs commands

[Ecniv]

I haven't used it in a while.

The following should print the caption used for the edit button.

?commandbars("Menu Bar").Controls("&Edit").Caption

If you use .Find, I think it is msoControlButton or msoControlPopUp (the former being a normal button, the latter being a drop list in the toolbar/menu).

If you know the name and path to get there you should be able to .Execute it...

Example:

commandbars("Menu Bar").Controls("&Edit").Controls("&Paste").Execute

The only problem is that the menus may have more than one control with the same caption.
And if it is not visible then you need to get the next one in.

Best thing to test any of this would be to loop through all the controls and debug.print them. Then work up from there to executing the right one. If it is a built in control it may have a unique ID that doesn't change, that you could use.

[sugarflux]

You could use the following code to search for the command you are after....

Dim myBar as CommandBar
 
for each myBar in Application.CommandBars
  msgbox myBar.name & CHR(13) & myBar.Caption
next

will pop up each command bar nam and description in turn and:

Dim myCtrl as control
For Each myCtrl in Application.CommandBars("TheCommandBarName").Controls
  msgbox myCtrl.Name & CHR(13) & myCtrl.Caption
next

will pop up each control name and description in turn for the selected commandbar.

Hope this helps,
sugarflux

[posto]

a quick question from me.I have quite the same problem here.but what i wanna ask is, is it possible to create a programs via VB that are able to control the embedded spreadsheet inside UG?because you have to open UG first only then will u be able to open the spreadsheet.the spreadsheet however cannot be exported/imported which makes it difficult to update a part from outside of UG.Any ideas?Thanks!