VB6 Form in Foreground for Excel

[Rick]

Hi,

I've written a more complex program in VB6 with a single
form, so that I can use it for work in Excel multiple
times. This way, I don't have to use VBA code in the
modules. It works great! I want to thank people who have
helped me here in the past. It's been a part time project
for me over the months - and it's been a lot of fun.

I have one problem. How do I get the VB6 form to stay in
the foreground? With Excel 2000 and better we could write
this with VBA in a module (for example): "frmText.Show
vbModeless" and it worked fine. But now with VB6, I can't
do the same, so that the VB6 form is in the foreground.

I found something interesting on the internet that was
this:

Const HWND_TOPMOST = -1
Const HWND_NOTOPMOST = -2
Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const SWP_NOACTIVATE = &H10
Const SWP_SHOWWINDOW = &H40
Private Declare Sub SetWindowPos Lib "User32" (ByVal hWnd
As Long, ByVal hWndInsertAfter As Long, ByVal X As Long,
ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal
wFlags As Long)

Private Sub Form_Activate()
'Set the window position to topmost
SetWindowPos Me.hWnd, HWND_TOPMOST, 0, 0, 0, 0,
SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or
SWP_NOSIZE
End Sub

Would this work for a VB6 form to keep it in the
foreground? Is there another better way? I appreciate
the help. I thought I would run it by a few of you before
I started experimenting with this code and others that I
might find later.

Thanks, Rick

 

[Chip Pearson]

Rick,

You can use the SetParent API function to set Excel's main window as the
parent window of your VB6 form. E.g.,

Public Declare Function SetParent Lib "user32" ( _
ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) As Long

Sub SetTheParent()
Dim XLHWnd As Long
XLHWnd = XL.hWnd
SetParent Form1.hWnd, XLHWnd
End Sub

where XL is the reference to the Excel application.

 

[Rick]

Thanks Chip.

I tried using the code that you provided, but it would not
work for me (including switching around the child-parent
order). I understand the logic, and it read the code
every time I activated a command from a button on the
form. It didn't fail with an error - it just didn't bring
the VB6 form to the foreground - in front of the Excel
worksheet - and leave it there.

I changed the XL to the Excel application that I was using
(which was defined as m_XLWorkbook). For Form1.hWnd I
changed it to Me.hWnd.

But, I did discover something that I accidentally did,
that now is working better. I accidentally set
ShowInTaskbar to be False instead of True (in the Form
properties). That's fixed part of it.

I'll keep reviewing this. Maybe I've not inserted your
code in the proper place or have done something else
wrong. Thanks so much for the help.

Thanks, Rick