Create and show UserForm from Add-In
 

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)

Create and show UserForm from Add-In
 

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)

Create and show UserForm from Add-In
 

Hi Rick,

There is some truth in Robin's post.
But if you really need to have a new form in the Active workbook, wh
don't you add a procedure for calling the added form? I think it's a
easy way.�@



Code
-------------------

Option Explicit

Sub Test()
' Create the form in the active workbook
Dim objForm As VBComponent
Dim ctrl As Object
Dim Wb As Workbook
Dim ret As Boolean
Set Wb = Workbooks.Add 'For Testing
Set objForm = Wb.VBProject.VBComponents.Add(vbext_ct_MSForm)

With objForm
' Set the forms properties
.Name = "MyCustomForm"

' Add controls to the form
Set ctrl = .Designer.Controls.Add("Forms.ListBox.1", "SourceListBox", True)
ctrl.Top = 10: ctrl.Left = 10: ctrl.Width = 200: ctrl.Height = 60
Set ctrl = .Designer.Controls.Add("Forms.ListBox.1", "ChosenListBox", True)
ctrl.Top = 80: ctrl.Left = 10: ctrl.Width = 200: ctrl.Height = 60
Set ctrl = .Designer.Controls.Add("Forms.CommandButton.1", "AddButton", True)
ctrl.Top = 140: ctrl.Left = 75: ctrl.Width = 50: ctrl.Height = 20: ctrl.Caption = "ADD"
End With

' Show the form
ret = ShowUserformInAnotherWkb(Wb, objForm)
If ret Then
MsgBox "Succeed!"
Else
MsgBox "Error"
End If
End Sub

Function ShowUserformInAnotherWkb(ByVal Wb As Workbook, ByVal objUF As Object) As Boolean
Dim objStdMod As VBComponent
Dim strCode As String
On Error GoTo Terminate
strCode = "Sub ShowForm():" & objUF.Name & ".Show:End Sub"
Set objStdMod = Wb.VBProject.VBComponents.Add(1)
With objStdMod
With .CodeModule
.DeleteLines 1, .CountOfLines
.InsertLines 1, strCode
End With
End With
Application.Run Wb.Name & "!ShowForm"
Wb.VBProject.VBComponents.Remove objStdMod
Set objStdMod = Nothing
ShowUserformInAnotherWkb = True
Exit Function
Terminate:
End Function

-------------------


-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Create and show UserForm from Add-In
 

Thanks for your help guys, it worked a treat!

I dynamically created a function in my workbook, that returned a
reference to the newly created user form (ie. VBA.UserForms.Add(...)).

Once I had the reference, in my XLA, I could manipulate the controls
and finally show the form.

Thanks once again.
Cheers
Rick :-)