Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form question
Leon;407572 Wrote: 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/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113600 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form question
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 On 8 Jul., 02:13, Leith Ross wrote: Leon;407572 Wrote: 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/) ------------------------------------------------------------------------ Leith Ross's Profile:http://www.thecodecage.com/forumz/member.php?userid=75 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=113600 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form question
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/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113600 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form question
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 On 8 Jul., 18:39, Leith Ross wrote: 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/) ------------------------------------------------------------------------ Leith Ross's Profile:http://www.thecodecage.com/forumz/member.php?userid=75 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=113600 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form question | Excel Discussion (Misc queries) | |||
Form Question | Excel Programming | |||
Form Question | Excel Programming | |||
Form Question | Excel Programming | |||
ser form question | Excel Programming |