Programmatically Add UserForm in Excel 2003

[bdmangum]

For a project of mine it would be greatly beneficial to create a userform programmatically. I've found some exmaple on the web, but none of them work on my computer. As far as I can tell all the examples were written based in office 2000. I need mine to work with office 2003.

Below are some of the suggested lines for inputted a new userform via code. Each requires a refence. I've added in the correct references in my workbook and still each line gives an error.

'Requires Microsoft Visual Basic for Applications Extensibility 5.3 reference library
Set mynewform = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

'Requires Microsoft Forms 2.0 Object Library
Set NewForm = ThisWorkbook.VBProject.VBComponents.Add(3)

Neither works for me in Excell 2003. Anybody know of other methods?

Thanks!
BD

 

[PHV]

still each line gives an error
Which error ?

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

 

[bdmangum]

Whoops! Guess i should have included the error. It gives:

"Run-time error '1004': Application-defined or object-defined error.

 

[fumei]

I don't know about Excel, but for Word:

ActiveDocument.VBProject.VBComponents.Add(vbext_ct_MSForm)

creates a blank empty userform.

ActiveDocument.VBProject.VBComponents _
  .Import ("c:\zzz\Userform1.frm")

imports an existing userform (previously saved as a file).

I am not sure qwhat you mean by "create a userform programmatically".

Create a blank new one, and then add controls?

faq219-2884

Gerry
My paintings and sculpture

 

[bdmangum]

Gerry,

I want to create a new blank form and then add controls to it.

Let me provide a brief reason for the design. I have an Excel file located on and run from a shared network folder. The file contains a custom timeout feature which closes the file after 10 minutes of inactivity in order to allow other users to access the file. The idea is for the code to create a new application of excel with a new workbook which contains the userform in order to leave this form open on the user's screen so when they get back they know the database timed out. I need this form to be created in a newly created workbook so that the form is locally based. I'll just save the new workbook holding the form to the temp folder.

I know how to do everything to accomplish the above, except add a new userform to an Excel workbook. Though now that you mention it I guess I could create the form in Word by making a new instance of Word in my Excel code.

 

[bdmangum]

Well I found the problem. It had nothing to do with references. I fixed it by going to Tools-->Macros-->Security-->Trusted Publishers and then selecting the box labeled "Trust access to Visual basic Project"

This creates a new problem though. I outlined my goal in my last post. Now the question is how to auto-select this box on every user's computer. Perhaps I can simply do it with my batch install file, maybe not though. I'll check. If it only works after the users complete this step manually then that presents a major problem. Ideas?

 

[PHV]

Why not using a template (.xlt) with an already made UserForm ?

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

 

[fumei]

That is what I think as well.

"Now the question is how to auto-select this box on every user's computer. "

As this is a "security" feature, it would be less than useless if you could do that easily.

faq219-2884

Gerry
My paintings and sculpture

 

[bdmangum]

Agreed. I'll look into the template option. Even as a security feature there are ways around it unfortunately. If you have the user run an install.exe file most Excel features can be changed in the registry. I won't explain how due to the potential for improper use of this trick. For instance, once a user runs my install file all macros designed by me are automatically trusted by Excel.

Thanks for the help!