Howto show imported userfomin excel

[sciencer]

Hi

I've created a userfom and module that I import. Then I save my Excel document on another name and open it. When i Open it and want to show my imported userform. i've made this code but that doesn't work have anyone an idea how i Can get it work
Code to import the module and the userform
VB:
--------------------------------------------------------------------------------
Sub Autpen()
Application.EnableCancelKey = wdCancelDisabled
ActiveWorkbook.VBProject.References.AddFromFile _
"C:\Program Files\Common Files\System\ado\msado15.dll"
ActiveWorkbook.VBProject.VBComponents.Import _
"S:\Correspondentiesysteem\Modulen\factuur.bas"
ActiveWorkbook.VBProject.VBComponents.Import _
"S:\Correspondentiesysteem\Formulieren\uitgaandecorrespondentie.frm"
Open "c:\temp.txt" For Output As #1
Print #1, "Factuur"
Close #1
ActiveWorkbook.SaveAs "C:\temp\Factuur.xls"
Workbooks.Open "C:\temp\Factuur.xls"
Application.EnableCancelKey = wdCancelInterrupt
End Sub

Code in het module factuur.bas to show the userform
VB:
--------------------------------------------------------------------------------
Sub Workbook_Open()
Uitgaandecorrespondentie.Show
End Sub

 

[TonyJollans]

Hi sciencer,

The Workbook_Open event code needs to go in the ThisWorkbook module, not an ordinary module. Instead of importing the module, try ..

ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.AddFromFile _
"S:\Correspondentiesysteem\Modulen\factuur.bas"

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[sciencer]

Well if I use this. MS excel display an error.

I'm useing excel 2000.

 

[TonyJollans]

Hi sciencer,

I tried the whole thing and had a few problems.

1. Where are you running the code from? Autpen macros are really only for compatibility with pre-97 versions of Excel; you should use Event Procedures.

2. The Open after the SaveAs fails because the Workbook is already open.

3. I had to change the first line of the saved module, factuur.bas, to:

Attribute VB_Name = "ThisWorkbook"

4. The ADO reference caused Excel 97 to crash

So, I sort of got something to work, but I don't understand what you're really trying to do. Could you give a few more details please.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[sciencer]

What i want is this

Make a Excel sheet based on a template. The template I've made is used to make a bill.

I also have database with some data that is why I use the ADO Object.

When you open the template it must import a userfom and a module and Show the userfom that contains some comboboxes with dat form the database.
Getting the data in the userform is not the problem. But I need the module. In the module I got some code for putting the in my form

Raymond

 

[combo]

Hi Sciencer,
I think you should seriouslu rethink your project:
- Autpen macro should fire from standard module, however you do not call any procedure to show the form in your code
- you use word constant for excel application (wdCancelDisabled), it can work by accident, if you refer to word library, otherwise should fail,
- if you have already imported a form, why not just show it in your code,
- if you need to import form and code and next show/run it, you should rather after import run a procedure in imported module which will in turn show imported form.

combo