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?

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------

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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
Howard,

I wasn't going to reply because I don't think I understand your problem
(particularly
the significance of your line 'The VB code shows the Form 3 times.').
However, nobody
else has replied, so you may wish to know that a VB6 dll used from Excel
can display
a VB6 form correctly (modeless or modal). Is that of any interest to you?

Regards,
Rob Rutherford


"Howard Kaikow" wrote in message
...
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?

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------

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" wrote in message ...
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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
Howard,

I wasn't going to reply because I don't think I understand your problem
(particularly
the significance of your line 'The VB code shows the Form 3 times.').
However, nobody
else has replied, so you may wish to know that a VB6 dll used from Excel
can display
a VB6 form correctly (modeless or modal). Is that of any interest to you?

Regards,
Rob Rutherford


"Howard Kaikow" wrote in message
...
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?

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------

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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
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" wrote in message
...
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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
Howard,

I wasn't going to reply because I don't think I understand your
problem
(particularly
the significance of your line 'The VB code shows the Form 3 times.').
However, nobody
else has replied, so you may wish to know that a VB6 dll used from
Excel
can display
a VB6 form correctly (modeless or modal). Is that of any interest to
you?

Regards,
Rob Rutherford


"Howard Kaikow" wrote in message
...
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?

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------

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" wrote in message ...
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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
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" wrote in message
...
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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
Howard,

I wasn't going to reply because I don't think I understand your
problem
(particularly
the significance of your line 'The VB code shows the Form 3 times.').
However, nobody
else has replied, so you may wish to know that a VB6 dll used from
Excel
can display
a VB6 form correctly (modeless or modal). Is that of any interest to
you?

Regards,
Rob Rutherford


"Howard Kaikow" wrote in message
...
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?

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------

I'll take a look at this.

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

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
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" wrote in message
...
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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
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" wrote in message
...
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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
Howard,

I wasn't going to reply because I don't think I understand your
problem
(particularly
the significance of your line 'The VB code shows the Form 3
times.').
However, nobody
else has replied, so you may wish to know that a VB6 dll used from
Excel
can display
a VB6 form correctly (modeless or modal). Is that of any interest
to
you?

Regards,
Rob Rutherford


"Howard Kaikow" wrote in message
...
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?

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------

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


"Howard Kaikow" wrote in message ...
My problem is a bit more complex, since the VBA Userform is actually calling
code in the VB class both before and after the VB Form is displayed.

As I indicated in an earlier posting, I believed that the solution likely
required grabbing the handle of the Userform and making it the parent of the
VB Form, or something like that. I have not yet applied my theory to my
particular problem, but I applied it to RR's example is given below. This
seems to expose the VB BForm to the user's nimble fingers.

I expect that the same technique can be applied to my actual problem.


Code in VBA Userform is:

Option Explicit
Private Declare Function GetActiveWindow Lib "user32" () As Long

Private Sub CommandButton1_Click()
Dim obj As New RRTestDLL.Class1
obj.ShowForm1 GetActiveWindow()
End Sub


Code in VBA moduled is:

Option Explicit

Public Sub ShowUserForm()
frmVBAUserform.Show
End Sub

Code in VB class is:

Option Explicit
Private Declare Function GetActiveWindow Lib "user32" () As Long
Private Declare Function SetParent Lib "user32" _
(ByVal hWndchild As Long, ByVal hWndNewParent As Long) As Long

Public Sub ShowForm1(lngVBAUserformHandle As Long)
Dim lngSetParent As Long
Form1.Show vbModal
lngSetParent = SetParent(GetActiveWindow(), lngVBAUserformHandle)
End Sub

Code in V BForm is:

Option Explicit

Private Sub Command1_Click()
MsgBox "I've been clicked"
End Sub


--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
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" wrote in message
...
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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
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" wrote in message
...
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.

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------
"Rob Rutherford" wrote in message
...
Howard,

I wasn't going to reply because I don't think I understand your
problem
(particularly
the significance of your line 'The VB code shows the Form 3
times.').
However, nobody
else has replied, so you may wish to know that a VB6 dll used from
Excel
can display
a VB6 form correctly (modeless or modal). Is that of any interest
to
you?

Regards,
Rob Rutherford


"Howard Kaikow" wrote in message
...
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?

--
http://www.standards.com/; Howard Kaikow's web site.
------------------------------------------------