Show a Excel UserForm in Access

[qing yun]

Hi,

I am working on an application of which the front end is Access, the
backend is Excel. Basically all important parameter values are stored
in the Access, which calls Excel to do the modeling work. In the end
some of the results would be saved in the access.

As the modeling Macro takes some time, I implemented a progress bar in
excel to let people know the status of the job. I made one by following
the J-Walker's example. http://j-walk.com/ss/excel/tips/tip34.htm. It
runs greatly.

But I want to show the Userform(progress bar) When people working in
the Access. I don't want people to see the excel file, so in automation
I use the default,setting, which means the opened excel file is
invisible. But I can not see the progress bar either. Is there some way
to bring the excel user form to the front? I tried different ways, such
as set the visible property of the excel file at different places;The
results is not satisfactory. after doing some reseach, I think calling
some Win API may be helpful. I tried "SetForegroundWindow", and
"FindWindow", but it still does not work.

If Anyone has experieces in this, Can you give me some suggestions?

Thanks
mark li

 

[Leith Ross]

Hello Mark,

This code will keep your UserForm on top of all other forms. Add a VB
Module to your project and copy the code below into it.

CALLING THE MACRO
In the UserForm Activate event code module insert the following...
KeepFormOnTop


Code
-------------------

Private Declare Function SetWindowPos _
Lib "user32.dll" _
(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) As Long

'Returns the Window Handle of the Active Window
Public Declare Function GetActiveWindow _
Lib "user32.dll" () As Long

Public Sub KeepFormOnTop()

Dim hWnd As Long

Const HWND_NOTOPMOST = -2
Const HWND_TOPMOST = -1
Const SWP_NOMOVE = &H2
Const SWP_NOSIZE = &H1

hWnd = GetActiveWindow()

SetWindowPos hWnd, HWND_TOPMOST, 0, 0, 0, 0, _
SWP_NOMOVE + SWP_NOSIZE

End Sub

 

[qing yun]

Hi Leith,

Thanks very much for your help. That is exactly what I want to know.
Have a great holiday.

Mark