Rick,

I presume that there is a need to build the form dynamically, but it is
possible to have a protected addin with a blank form in it and add controls
to the form in code. i.e. the form already exists in the addin but the
controls on it don't. If you do it that way there is no need to try and call
a form in another book.

Robin Hammond
www.enhanceddatasystems.com


"Rick Knight" wrote in message
om...
Hi,

I have am running Excel 2002, and have written an Add-In (.xla).

Upon starting Excel, the add-in dynamically builds a menu. When the
user clicks one of the menu items, the add-in then dynamically creates
a user form (of type vbext_ct_MSForm), in the currently active
workbook. This form is then populated with controls (command buttons
and the like) and then tries to display it modally.

Because the add-in is protected, it can not be modified, so therefore
I have had to create the UserForm in the ActiveWorkbook. The problem
is that, once the form is created, I can not figure out how to show
it.

Refer to the sample code snippet below.

On line 13 I get the error "424 - Object Required". This is because
'MyCustomForm' was added to the ActiveWorkbook not the add-in's
workbook.

But, If I comment back in line 14, I get the compile error: "Method or
Data Member Not Found".

Can anyone help!

Thanks in advance.
Rick.

01 ' Create the form in the active workbook
02 Dim objForm As VBComponent
03 Set objForm =
ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct _MSForm)
04
05 ' Set the forms properties
06 objForm.Properties("Name") = "MyCustomForm"
07
08 ' Add controls to the form
09 ...
10
11 ' Add the user form to the UserForms collection
12 Dim myForm as Object
13 Set myForm = VBA.UserForms.Add("MyCustomForm")
14 'Set myForm = VBA.ActiveWorkbook.UserForms.Add("MyCustomForm")
15
16 ' Show the form
17 Call myForm.Show(vbModal)