Detect Excel application minimize/restore?
in [Visual Basic]
|
From: Peter T on 10 Dec 2009 09:03 "Karl E. Peterson" <karl(a)exmvps.org> wrote in message > Karl E. Peterson submitted this idea : >> RB Smissaert formulated the question : >>>>> Had a quick look and it looks all very good. >>>>> Just one problem I noticed is that when I showed the VBA form modeless >>>>> and then minimized Excel the form was gone, I suppose it crashed. Can >>>>> it work with a modeless form? >>>> >>>> I have no idea, as I've never tried it. It's hard to think of a >>>> reason, off the top of my head, why it wouldn't. Using callbacks in >>>> VBA is inherently dangerous, of course. I suppose I could take a look >>>> at it, but probably not today, unless some sort of miracle happens. <g> >>> >>> No hurry, I don't in fact need this, just trying to help out the OP plus >>> it is kind of interesting. >> >> Hmmm, seems to work here. Yes, when I minimize Excel, the modeless form >> disappears too. But that's because it's owned by the main app, I would >> think. When I restore Excel, the modeless form pops right back up with >> it. >> >> Testing in Office 2003 on Windows 7 x64. > > Okay, yeah, this is weird. I am seeing that in some cases, the userform > isn't being restored along with the application. (This didn't start > happening until I added some code to decouple the userform from its > owner.) If I set the userform to not have an owner, it stays visible when > the application is minimized, and I haven't seen it disappear yet. > Something's up, and I can't say what it is at this point. Just been pointed to this thread by RBS off-line, he saw I had posted quite a lot of stuff for the OP in .excel.programming Briefly, the OP wants the Userform's parent toggled to the desktop when Excel is minimized, and reset when normal/maximized (and in both events for the form to be in front) As discussed here, Excel does not expose minimize/restore events, so the only way is with a timer. It is possible to do all this with an API timer in VBA, but I didn't want to post the method (risk of unwitting user interferring with the running code and crashing Excel). Anyway, in my VBA timer that monitors Excel's window state, when changing the form's parent simply - SetWindowLongA mhWndFrm, GWL_HWNDPARENT, excel-app.hWnd or 0& SetForegroundWindow mhWndFrm mFrm.Hide mFrm.Show vbModeless Where mhWndFrm refers to the form's handle, and mFrm to the previously loaded and already showing form. Regards, Peter T
From: Karl E. Peterson on 10 Dec 2009 14:18 Peter T pretended : > "Karl E. Peterson" <karl(a)exmvps.org> wrote in message >> Karl E. Peterson submitted this idea : >>> RB Smissaert formulated the question : >>>>>> Had a quick look and it looks all very good. >>>>>> Just one problem I noticed is that when I showed the VBA form modeless >>>>>> and then minimized Excel the form was gone, I suppose it crashed. Can >>>>>> it work with a modeless form? >>>>> >>>>> I have no idea, as I've never tried it. It's hard to think of a >>>>> reason, off the top of my head, why it wouldn't. Using callbacks in >>>>> VBA is inherently dangerous, of course. I suppose I could take a look >>>>> at it, but probably not today, unless some sort of miracle happens. <g> >>>> >>>> No hurry, I don't in fact need this, just trying to help out the OP plus >>>> it is kind of interesting. >>> >>> Hmmm, seems to work here. Yes, when I minimize Excel, the modeless form >>> disappears too. But that's because it's owned by the main app, I would >>> think. When I restore Excel, the modeless form pops right back up with >>> it. >>> >>> Testing in Office 2003 on Windows 7 x64. >> >> Okay, yeah, this is weird. I am seeing that in some cases, the userform >> isn't being restored along with the application. (This didn't start >> happening until I added some code to decouple the userform from its >> owner.) If I set the userform to not have an owner, it stays visible when >> the application is minimized, and I haven't seen it disappear yet. >> Something's up, and I can't say what it is at this point. > > Just been pointed to this thread by RBS off-line, he saw I had posted quite > a lot of stuff for the OP in .excel.programming > > Briefly, the OP wants the Userform's parent toggled to the desktop when > Excel is minimized, and reset when normal/maximized (and in both events for > the form to be in front) What's that mean - toggled to the desktop? > As discussed here, Excel does not expose minimize/restore events, so the > only way is with a timer. Not sure it's the only way, but it's the most straight-forward way for sure. > It is possible to do all this with an API timer in > VBA, but I didn't want to post the method (risk of unwitting user > interferring with the running code and crashing Excel). Understood. <g> > Anyway, in my VBA timer that monitors Excel's window state, when changing > the form's parent simply - > > SetWindowLongA mhWndFrm, GWL_HWNDPARENT, excel-app.hWnd or 0& > SetForegroundWindow mhWndFrm > mFrm.Hide > mFrm.Show vbModeless > > Where mhWndFrm refers to the form's handle, and mFrm to the previously > loaded and already showing form. Why are you changing the modal state at that point as well? I thought it was modeless from the get-go. I was just setting the owner to 0& on Activate, and restoring the original owner on Deactivate. -- [.NET: It's About Trust!]
From: Karl E. Peterson on 10 Dec 2009 14:20 RB Smissaert laid this down on his screen : >> If I set the userform to not have an owner > > I have a feeling that this is what the OP wants, or the desktop to be the > owner. Still, as you say there is something fishy going on here. I think Office may be doing something fishy with window handles, and I need to take a closer look at the lifetime of the handle I'm using? > If this all works then that would be a major addition for VBA. One thing that > springs to mind is keep a form refreshed (repaint) if VBA is working > so hard that the form would otherwise go white. Basically it would be > a simple way to add threading or do I see that wrong? It wouldn't really be threading, per se, as much as a throttled state machine. <g> -- [.NET: It's About Trust!]
From: RB Smissaert on 10 Dec 2009 17:54 Your mentioning of your timer gave me an idea and that is that VBA does in fact have a crude timer and that is called Application.OnTime. With that it was reasonably simple to do what the OP wanted. All this is in VBA: To demonstrate this all you need is a userform with only this code in it: Option Explicit Private Sub UserForm_Terminate() TimerOff End Sub Then there is a module with all this code: Option Explicit Private Declare Function GetDesktopWindow Lib "user32" () As Long Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long Private Declare Function GetWindowThreadProcessId _ Lib "user32" (ByVal hwnd As Long, _ ByRef lpdwProcessId As Long) As Long Private Declare Function FindWindowEx Lib "user32" _ Alias "FindWindowExA" _ (ByVal hWnd1 As Long, _ ByVal hWnd2 As Long, _ ByVal lpsz1 As String, _ ByVal lpsz2 As String) As Long Private Declare Function FindWindow Lib "user32" _ Alias "FindWindowA" _ (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function IsIconic Lib "user32" (ByVal hwnd As Long) As Long Private Declare Function SetParent Lib "user32" (ByVal hWndChild As Long, _ ByVal hWndNewParent As Long) As Long Private bTimerEnabled As Boolean Private dTimerInterval As Double Private lExcelHwnd As Long Private lFormHwnd As Long Private lExcelWindowState As Long Private lExcelWindowStatePrevious As Long Sub LoadForm() Load UserForm1 UserForm1.Show 0 lExcelHwnd = GetExcelHwnd() lFormHwnd = GetFormHwnd(UserForm1.Caption) bTimerEnabled = True dTimerInterval = TimeValue("00:00:01") lExcelWindowStatePrevious = -1 'start the timer RunTimer End Sub Sub TimerOff() bTimerEnabled = False End Sub Sub SetFormParent() lExcelWindowState = IsIconic(lExcelHwnd) If lExcelWindowState <> lExcelWindowStatePrevious Then If lExcelWindowState = 0 Then SetParent lFormHwnd, lExcelHwnd Else SetParent lFormHwnd, 0 End If End If lExcelWindowStatePrevious = lExcelWindowState End Sub Sub RunTimer() SetFormParent If bTimerEnabled Then Application.OnTime (Now + dTimerInterval), "RunTimer" End If End Sub Function GetExcelHwnd() As Long '------------------------------------------------------------ 'Finds a top-level window of the given class and 'caption that belongs to this instance of Excel, 'by matching the process IDs 'Arguments: sClass The window class name to look for ' sCaption The window caption to look for 'Returns: Long The handle of Excel's main window '------------------------------------------------------------ Dim hWndDesktop As Long Dim hwnd As Long Dim hProcThis As Long Dim hProcWindow As Long Dim sClass As String Dim sCaption As String If Val(Application.Version) >= 10 Then GetExcelHwnd = Application.hwnd Exit Function End If sClass = "XLMAIN" sCaption = Application.Caption 'All top-level windows are children of the desktop, 'so get that handle first hWndDesktop = GetDesktopWindow 'Get the ID of this instance of Excel, to match hProcThis = GetCurrentProcessId Do 'Find the next child window of the desktop that 'matches the given window class and/or caption. 'The first time in, hWnd will be zero, so we'll get 'the first matching window. Each call will pass the 'handle of the window we found the last time, thereby 'getting the next one (if any) hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption) 'Get the ID of the process that owns the window we found GetWindowThreadProcessId hwnd, hProcWindow 'Loop until the window's process matches this process, 'or we didn't find the window Loop Until hProcWindow = hProcThis Or hwnd = 0 'Return the handle we found GetExcelHwnd = hwnd End Function Function GetFormHwnd(strCaption As String) As Long If Val(Application.Version) >= 9 Then GetFormHwnd = FindWindow("ThunderDFrame", strCaption) Else GetFormHwnd = FindWindow("ThunderXFrame", strCaption) End If End Function Load the form with Sub LoadForm I am sure it can all be refined, but these are the basics and it works well with me. RBS "Karl E. Peterson" <karl(a)exmvps.org> wrote in message news:uKGZp3ceKHA.1592(a)TK2MSFTNGP06.phx.gbl... > RB Smissaert laid this down on his screen : >>> If I set the userform to not have an owner >> >> I have a feeling that this is what the OP wants, or the desktop to be the >> owner. Still, as you say there is something fishy going on here. > > I think Office may be doing something fishy with window handles, and I > need to take a closer look at the lifetime of the handle I'm using? > >> If this all works then that would be a major addition for VBA. One thing >> that springs to mind is keep a form refreshed (repaint) if VBA is working >> so hard that the form would otherwise go white. Basically it would be a >> simple way to add threading or do I see that wrong? > > It wouldn't really be threading, per se, as much as a throttled state > machine. <g> > > -- > [.NET: It's About Trust!] > >
From: Karl E. Peterson on 10 Dec 2009 18:25
RB Smissaert presented the following explanation : > Your mentioning of your timer gave me an idea and that is that VBA does in > fact have a > crude timer and that is called Application.OnTime. Hey, there ya go. I have really stumbled into something, it seems. I added a brand new form to the project, and tried loading it modeless. When minimized, it too was destroyed, just like the one that was coded to use the timer. So now I have no idea what's up. This doesn't happen if I do it in another project, of course. Very weird. -- [.NET: It's About Trust!] |