Open a form from an Add-In

[scott]

I keep my custom menu in an add-in mymenu.xla. I'm trying to open a form
called "frm_enterData" that resides in a seperate workbook called
myWorkbook. A snipplet of my mymenu.xla menu code that should open the form
is listed in CODE 2.

Do I need to specify the path of the form's workbook's path before the name
of the function that opens the form? If so, what would the syntax be?

CODE:

Sub openDataForm()
frm_enterData.Show
End Sub


CODE 2:

Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Enter Data"
.OnAction = "openDataForm"
End With

 

[carlo]

you have to tell excel, where the form is located
otherwise it looks in the xla:

Sub openDataForm()
workbooks("myWorkbook").frm_enterData.Show
End Sub

hth

Carlo

 

[Dave Peterson]

I'd use:

Dim wkbk As Workbook
Dim MenuItem As CommandBarControl
Dim NewMenu As CommandBar

Set wkbk = Workbooks("myaddin.xla")

Set NewMenu = Application.CommandBars("somename here")

Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton, temporary:=True)
With MenuItem
.Visible = True
.Style = msoButtonCaption
.Caption = "Enter Data"
.OnAction = "'" & wkbk.Name & "'!openDataForm"
End With

 

[scott]

The name of my excel file is actually "bol_tracking.xls" and the worksheet
is named "BOL Match".

What would the hard-coded syntax be? I tried your way, but couldn't get it
going.

 

[scott]

The sub "openDataForm" exists within the same add-in as the menu code. It's
the form that's being called in the openDataForm sub. I need the long syntax
to reference the form that resides in the workbook called
"c:\data\my_workbook.xls".

Sub openDataForm()
frm_enterData.Show
End Sub

 

[Dave Peterson]

Put a procedure in workbook's project that contains the userform. Then you can
call that procedure.