Create form from code: Library & Trust issue 1

[JTBorton]

I pulled this code from an Eng-Tips faqs,

http://www.eng-tips.com/faqs.cfm?fid=110,

but I am having a lot of trouble with it. I'm trying to create a form from a class module.

I added the reference library "Microsoft Visual Basic for Applications Extensibility 5.3" but it still cannot find the library for MSForms.

Dim NewButton As MSForms.CommandButton
[b][COLOR=red]Compile error:
User-defined type not defined.[/color][/b]

So I changes the variable NewButton from MSForms.CommandButton to just an Object.

Next it crashed on the line that reads

Application.VBE.MainWindow.Visible = False
[b][COLOR=red]Run time error '1004'
Method 'VBE' of object '_Application' failed.[/color][/b]

So I commented it out and tried to press forward. Finally it crashed again when I tried to create the form:

Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
[b][COLOR=red]Run time error '1004'
Programmatic access to Visual Basic is not trusted[/color][/b]

Ya I don't know how to work around that one. I'm using Excel 2003 prof. The .Add(vbext_ct_MSForm) did pop up in the intellisense, so it found something but it seems that the reference library wont register - or something like that. Any clues?

Sub MakeForm()
    Dim TempForm As Object ' VBComponent
    Dim FormName As String
    Dim NewButton As Object [highlight][COLOR=green]'MSForms.CommandButton[/color][/highlight]
    Dim TextLocation As Integer
'   ** Additional variable
    Dim X As Integer
 
    'Locks Excel spreadsheet and speeds up form processing
    [highlight][COLOR=green]'Application.VBE.MainWindow.Visible = False[/color][/highlight]
    Application.ScreenUpdating = False
    
'   Create the UserForm
    [highlight][COLOR=red]Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)[/color][/highlight]
    
    'Set Properties for TempForm
    
    TempForm.Name = "frmMyForm"
    
    With TempForm
        .Properties("Caption") = "Temporary Form"
        .Properties("Width") = 200
        .Properties("Height") = 100
    End With
    FormName = TempForm.Name
 
'   Add a CommandButton
    Set NewButton = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewButton
        .Caption = "Click Me"
        .Left = 60
        .Top = 40
    End With

-JTBorton
Another Day, Another Disaster

 

[PHV]

References:
Microsoft Forms 2.0 Object Library
Trust:
menu Tools -> Macro -> Security ...
tab Approved -> Tick the Trust VB project checkbox

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

Eh, ok I got the security, but I do not have a Microsoft Forms 2.0 Object Library. Not on this computer anyway. Seriously, I checked four times. Is there somewhere I can get it? I just left it as an object variable and marched on but I ran into two more problems:

TempForm.Name = "frmMyForm"

[b][COLOR=red]Run time error '75'
Path/File access error[/color][/b]

Is there a way to specify the name of the form? Not that it is critical that I do, just trying to learn.

The next issue is that when I try to insert code into the form it gives me the following prompt:

Can't enter break mode at this time

And it gives me the option to Continue or End. If I continue it cruises along, but I don't want the users to have to mess with that. I thought Application.VBE.MainWindow.Visible = False would take care of this. Any suggestions?

'   Add a CommandButton
    Set NewButton = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewButton
        .Caption = "Click Me"
        .Left = 60
        .Top = 40
    End With
'   Add an event-hander sub for the CommandButton
    With TempForm.CodeModule
 
'   ** Add/change next 5 lines
'       This code adds the commands/event handlers to the form
        X = .CountOfLines
        [highlight].InsertLines X + 1, "Sub CommandButton1_Click()"[/highlight]
        .InsertLines X + 2, "MsgBox ""Hello!"""
        .InsertLines X + 3, "Unload Me"
        .InsertLines X + 4, "End Sub"
    End With
 
'   Show the form
    VBA.UserForms.Add(FormName).Show
'
'   Delete the form
    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
End Sub

-JTBorton
Another Day, Another Disaster

 

[PHV]

Do you raise the error when running the macro with no VBE window ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

PHV, I ran it without going step by step in the code window as you suggested and both problems disappeared - the first fun.

On the second run I got the Path/File access error. So I changed

TempForm.Name = "frmMyForm"

To

TempForm.Name = "frmMyForm[highlight]2[/highlight]"

And it magically worked again. But upon trial three it crashed. So here is where I am confused: if the form is being removed at the end of the procedure with

    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

Then why will it not let me reuse the form name?

By the way, my Forms 2.0 library magically reappeared after restarting excel. I also late-bound the procedure using

Dim TempForm As Object [COLOR=green]' VBComponent[/color]

and changed 

Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3) [COLOR=green]'3 = vbext_ct_MSForm[/color]

-JTBorton
Another Day, Another Disaster