Open a form from an Add-In
 

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

Open a form from an Add-In
 

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



On Dec 20, 11:35 am, "scott" wrote:
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

Open a form from an Add-In
 

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 wrote:

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

--

Dave Peterson

Open a form from an Add-In
 

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.

"carlo" wrote in message
...
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



On Dec 20, 11:35 am, "scott" wrote:
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

Open a form from an Add-In
 

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

Open a form from an Add-In
 

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

scott wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson