Form question

[Leon]

Hi Folks

I have an application wich using an ontime procedure to schedule a
refresh task - updating cells with external data.
That takes a 30 seconds and a userform with a progressbar is shown
while updating.
That works fine if only Excel application have focus.

My problem is that I want Excel to updata even if I have an other
application open and that other application is active. Say Word.

If I have overlapping windows .. Excel in background and word on top
-
I want the progressbar to show up in front of Excel but behind Word..
The progressbar schould be updated while refreshing data to the sheet.
The form schould then be closed in the end,
now tha data is refreshed properly.
Is that to be done?

Cheers

 

[Leith Ross]

Hi Folks

I have an application wich using an ontime procedure to schedule a
refresh task - updating cells with external data.
That takes a 30 seconds and a userform with a progressbar is shown
while updating.
That works fine if only Excel application have focus.

My problem is that I want Excel to updata even if I have an other
application open and that other application is active. Say Word.

If I have overlapping windows .. Excel in background and word on top
-
I want the progressbar to show up in front of Excel but behind Word..
The progressbar schould be updated while refreshing data to the sheet.
The form schould then be closed in the end,
now tha data is refreshed properly.
Is that to be done?

Cheers

Hello Leon,

Copy this code into a standard VBA module in your project. Place a call
to the macro in the UserForm_Activate event code module.

'=========================================================
'Returns the Window Handle of the Window
'that is accepting User input.
Public Declare Function GetForegroundWindow Lib "user32.dll" () As
Long

Sub KeepFormOnTop()

Const HWND_TOPMOST As Long = -1
Const SWP_NOMOVE As Long = &H2
Const SWP_NOSIZE As Long = &H1

SetWindowPos GetActiveWindow(), HWND_TOPMOST, 0, 0, 0, 0, _
SWP_NOMOVE + SWP_NOSIZE

End Sub
'=========================================================
'
'UserForm Code
Private Sub UserForm_Activate()
KeepFormOnTop
End Sub
'=========================================================


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)

 

[Leon]

Hi Leith

GetActiveWindow() gives an error - not declared

I added this

'API Call Declarations
Public Declare Function GetActiveWindow _
Lib "user32.dll" () As Long

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

(by accident found (google) some of your code )

BUT.. I did not get the result i wanted.
The userform blow away the word application I had on top.

I want to operate in word as my excel applications OnTime schedule
updata data in excel - even thoug I work in Word, and furthermore Word
must stay on top.

If I now works in Word, Excel stops when trying to show the userform
with the progressbar..

How do I avoid this?

Cheers

 

[Leith Ross]

Hello Leon,

I should have proof read my post. I changed the GetActiveWindow in my
original routine to GetForegroundWindow because the latter has s
slightly higher priority and has proven to be more stable across
different Windows systems. the code should be this...

'================================================= ========
'Returns the Window Handle of the Window
'that is accepting User input.
Public Declare Function GetForegroundWindow Lib "user32.dll" () As
Long

Sub KeepFormOnTop()

Const HWND_TOPMOST As Long = -1
Const SWP_NOMOVE As Long = &H2
Const SWP_NOSIZE As Long = &H1

SetWindowPos GetForegroundWindow(), HWND_TOPMOST, 0, 0, 0, 0, _
SWP_NOMOVE + SWP_NOSIZE

End Sub
'================================================= ========


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)

 

[Leon]

Hi Leith

I appreaciate your help though it does not what I try to do.

When I activate my timer in Excel so that the progressbar form will be
shown in 5 seconds and schwitch to another application - then after 5
seconds Excel bring itselves in front and the userform comes on top of
it all.

That is not what I want.

I want Excel to stay in background, updating whatever and here my
problem is with the userform with progress bar.

Normally when the user works in Excel, the user schould be informed
that something is going on (userform with progressbar).
Then - when the user schwitches to another application, Excel schould
continue its scheduled automatic refreshing.. and here - I want the
userform with bar not to come on top... at the same time the code
schould continue.. updating the userform and progressbar - in
background.

What happends when modal is that code stops, and Excel icon on my
taskbar is blinking.
When I then activate Excel, the userform comes up.... It have been -
"waited" for my action... It schould have continued itself.

Hope it is understandable.

Cheers Leon