Problems running a macro in another workbook

[simonkue]

I have a confusing problem. I'm new to VBA but I do know VB6 well.
I have 2 Excel workbooks. Both contain a single worksheet. 'A' contains the data while 'B' contains
the Macros and a form I wish to use.
This means that I only have 1 place to maintain and update macros.
A macro (called Startup) in 'A' needs to display the form (from 'B') and allow me to select buttons

etc on the form and have an effect on 'A'.
The problem I have is that I cannot seem to get past the initial Open or run event and display the form in one pass.
The first time through, the worksheet is loaded but the form isn't displayed. When I run the same macro a second time via the same key sequence, the form is displayed and the buttons etc work as required. Incidentally, when I step through in the debugger, Test2 below works fine.

I've tried using the Workbook_Open or WorkbookOpen events but that code doesn't execute either.
I'm using Win2k and Office2k although this will be deployed in Office 97.

Code is as follows with three alternative attempts:

Sub ShowMacroForm()

Dim xsWrk As String
Dim xsWrk1 As String
Dim xsWorkBookName As String
Dim xwbMacroContainer As Workbook
Dim xshMacroContainer As Worksheet

xsWorkBookName = "InMacros.xls"
xsWrk = ActiveWorkbook.Path & "\" & xsWorkBookName

' Test 1
'MsgBox "Step 1."
'Workbooks.Open (xsWrk)
'MsgBox "Step 2" ' This line doesn't run. Once the open is done, nothing else happens

' Test 2
'xsWrk = "'" & ActiveWorkbook.Path & "\" & xsWorkBookName & "'!Startup"
'xsWrk1 = Application.ActiveWorkbook.Name
'Application.Run xsWrk, xsWrk1 ' First time the workbook is loaded but macro not run. 2nd time runs

OK

' Test 3
Set xwbMacroContainer = GetObject(xsWrk)
xwbMacroContainer.Activate
Application.Run "Startup", xsWrk1 ' Get error msg - cannot find the Startup macro

MsgBox "Step 4" ' This line isn't run

End Sub

Does anyone have any ideas where I'm going wrong please?

 

[SkipVought]

Hi,

In workbook A VB Editor, set a reference (Tools/References) to Workbook B. This will expose the procedures in workbook B.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[simonkue]

SkipVought,

Thanks for your help.

How do I get workbook 'B' onto the References list? Does it have to be a dll, in which case, do I need to somehow compile the modules?
I've tried renaming the 'B' project but still can't see it.

 

[SkipVought]

[Browse] and find it. YES, make the VBA Project nam UNIQUE.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}