Using an ActiveXDll with Excel 2002 when the DLL uses a VB Form and VBA uses a Userform

[Howard Kaikow]

I have an Excel workbook in which ALL code is in a class, other than the
code necessary to instantiate the class and load a VBA Userform. I then
compile the class with VB 6 and use the compiled DLL. No problem so far.

But then I got greedy and attempted to try something I've not tried before.

To the VB 6 class, I added a VB Form.
The VB code shows the Form 3 times.
If I make the Form modeless, as expected, all the user sees is the last
display of the Form.
If I make the Form modal, then Excel gets hung waiting for the VB Form to do
its thing each time it is displayed, so using vbModal appears to not work,
and I expected this.

How do I work around this?

Does VB have to get the handle of the VBA Userform and then make sure that
the VB Form gets displayed on top of the VBA Userform?
Or is there an easier way?

 

[Howard Kaikow]

The problem is that there's a VBA Userform running in Excel and Excel is not
playing nice with VB when I use a modal VB Form while the Excel Userform is
running.

I was thinking that I might try hiding the VBA Userform.

 

[Rob Rutherford]

If I open the VB6 form modally from an XL2002 userform, it seems OK - that is, the
userform sits there visible but I can't switch to it until I have finished with the VB form
and closed it. Is that what you want?

 

[Howard Kaikow]

The VB 6 Form is being opened from the compiled class, not from code within
the VBA Userform, so that may be an added complication.

 

[Rob Rutherford]

The only combination that seems to fail is trying to display a VB6 non-modal form
when a modal XL userform is being displayed. I don't know if it helps but this is
what I tried. This particular example shows both the VB6 form and the userform
modally.

VB6 project (RRTestDLL) has Class1 and Form1 containing a command button
called Command1.
-- Class1 code --
Public Sub ShowForm1()
Form1.Show vbModal
End Sub
-- Form1 code --
Private Sub Command1_Click()
MsgBox "I've been clicked"
End Sub

Excel 2002 workbook has Module1, and Userform1 with CommandButton1 on it.
-- Module1 code --
Public Sub DisplayVB6Form()
Dim obj As New RRTestDLL.Class1
obj.ShowForm1
End Sub
Public Sub ShowUserForm()
UserForm1.Show
End Sub
-- UserForm1 code --
Private Sub CommandButton1_Click()
Dim obj As New RRTestDLL.Class1
obj.ShowForm1
End Sub

I tested it by running DisplayVB6Form, or ShowUserForm and then clicking the button.

 

[Howard Kaikow]

I'll take a look at this.

Maybe I can construct a simple example that reproduces the behavior I am
seeing.

 

[Rob Rutherford]

As I suspected, you're probably out of my depth! I'll stop there.