Detect Excel application minimize/restore?
in [Visual Basic]
|
From: Karl E. Peterson on 18 Dec 2009 20:01 Hi Peter -- Sorry about the delay in response here. >>> 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? > > Sorry about the lazy casual layman's speak! > I meant doing this - > > If Excel is minimized H = 0 Else H = xlApp.Hwnd > SetWindowLongA userform-hWnd, GWL_HWNDPARENT, H Okay. I guess the next question is, why? >>> 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. > > I'm almost sure you have got some great other ways up your sleeve! See the post I must made to RB. You can use a CBT hook to be notified when each of these window states is about to change. It's pretty straight-forward, but the method of setting and removing the hook may be somewhat situational. > posted one way to do it with the help of a certain c-dll. Actually I posted > the link, instructions and light demo directly to *you* to solve this very > issue (how to detect Excel's resize event) > > http://groups.google.co.uk/group/microsoft.public.vb.general.discussion/browse_frm/thread/b7be3a5a1af39537/fa5a74ace42e6dc5 > > See my last post in the thread Wow, I guess I forgot all about that. Again, my apologies. The CBT hook will work perfectly safely for resizes. The lParam to HCBT_MOVESIZE: "Specifies a long pointer to a RECT structure containing the coordinates of the window. By changing the values in the structure, a CBTProc hook procedure can set the final coordinates of the window." http://msdn.microsoft.com/en-us/library/ms644977%28VS.85%29.aspx > It works well in VBA without hogging resources (I assume would work much > better in VB6 to trap any window messages of interest). Even so not entirely > safe if the VBE is open. Callbacks into an editor are always fun. <g> But CBT callbacks are rather limited, so they're not as potentially troublesome as an all-out message hook. >>> 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 >> >> 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. > > Not changing the modal state at all. Hiding then re-showing the form (I find) > gets over all sorts of problems that would otherwise involve parenting stuff > that I've never been able to get right (in this context). In this usage no > problem at all to re-show the form. > > In VBA the default userform.Show argument is vbModal. Therefore must include > vbModeless again otherwise all sorts of problems. > > RBS's suggestion of VBA's OnTime is of course a much more viable alternative > to anything involving a call-back in VBA. For this usage the one second > limitation is fine. Disappointed I didn't think of that myself! However I > think the window handling side of things is not quite right, I'm sure RB > won't mind me saying :-) I'll be curious what you guys think of using the CBT hook for these purposes. I don't really have any great call to test this with "real world" code, but theoretically "it oughta work." ;-) -- ..NET: It's About Trust! http://vfred.mvps.org
From: RB Smissaert on 19 Dec 2009 08:12 No trouble. I had a go with this, but I couldn't get it to work stable and as I have have a method that doesn't do call-backs and also because it looks the OP is not persuing this anymore I left it at that. I have now tried you code, but again I get crashes and not sure this can work reliably in VBA. Let me know and I send you the .xls as I have it now. Did you get it to work all fine in Excel, that is with the SetWindowLongA API to change the parent window? RBS "Karl E. Peterson" <karl(a)exmvps.org> wrote in message news:%23C849VEgKHA.2780(a)TK2MSFTNGP05.phx.gbl... > RB Smissaert explained on 12/10/2009 : >>>> Yes, best to stick to VBA stuff if you can and it looks we can. >>>> Only minor drawback is that the OnTime resolution is is one second so >>>> there is a small delay when minimizing and restoring Excel. >>> >>> Okay, interested in Plan B? >>> >>> Thought about a SetWindowsHookEx(WH_CBT) watching for HCBT_MINMAX? >> >> Yes, interested in that. > > Okay, sorry for taking so long to respond. Needed to find 15 minutes to > play. (And kinda thought the hint might be enough? <bg>) Anyway, it's > pretty basic. Here's one design. Start a new spreadsheet, and add a > UserForm and a standard Module. Put a command button on the spreadsheet, > and add this code behind it: > > *** Sheet1 ************************************************ > > Option Explicit > > Private Sub CommandButton1_Click() > Dim f As UserForm1 > Set f = New UserForm1 > f.Show vbModeless > End Sub > > *********************************************************** > > In the standard BAS module, add this code: > > *** Module1 *********************************************** > > Option Explicit > > Private Declare Function SetWindowsHookEx Lib "user32" Alias > "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod > As Long, ByVal dwThreadId As Long) As Long > Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook > As Long) As Long > Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long > > Private m_hHook As Long > Private m_Callback As UserForm1 > > Public Function SetCbtHook(obj As UserForm1) As Long > Const WH_CBT = 5 > m_hHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, 0&, > GetCurrentThreadId()) > If m_hHook Then Set m_Callback = obj > End Function > > Public Function UnhookCbt() > If m_hHook Then > Call UnhookWindowsHookEx(m_hHook) > Set m_Callback = Nothing > End If > End Function > > Private Function CBTProc(ByVal nCode As Long, ByVal wParam As Long, > ByVal lParam As Long) As Long > If Not m_Callback Is Nothing Then > CBTProc = m_Callback.CBTProc(nCode, wParam, lParam) > End If > End Function > > Public Function WordLo(ByVal LongIn As Long) As Integer > ' Low word retrieved by masking off high word. > ' If low word is too large, twiddle sign bit. > If (LongIn And &HFFFF&) > &H7FFF Then > WordLo = (LongIn And &HFFFF&) - &H10000 > Else > WordLo = LongIn And &HFFFF& > End If > End Function > > *********************************************************** > > Finally, in the UserForm, add this code: > > *** UserForm1 ********************************************* > > Option Explicit > > ' CBT Hook Codes > Private Const HCBT_MOVESIZE = 0 > Private Const HCBT_MINMAX = 1 > Private Const HCBT_QS = 2 > Private Const HCBT_CREATEWND = 3 > Private Const HCBT_DESTROYWND = 4 > Private Const HCBT_ACTIVATE = 5 > Private Const HCBT_CLICKSKIPPED = 6 > Private Const HCBT_KEYSKIPPED = 7 > Private Const HCBT_SYSCOMMAND = 8 > Private Const HCBT_SETFOCUS = 9 > > Private Const SW_MAXIMIZE = 3 > Private Const SW_MINIMIZE = 6 > Private Const SW_RESTORE = 9 > > Public Function CBTProc(ByVal nCode As Long, ByVal wParam As Long, ByVal > lParam As Long) As Long > Select Case nCode > Case HCBT_MOVESIZE > Debug.Print "HCBT_MOVESIZE", wParam, lParam > Case HCBT_MINMAX > Debug.Print "HCBT_MINMAX", wParam, lParam > If wParam = Application.Hwnd Then > Select Case WordLo(lParam) > Case SW_MINIMIZE > Debug.Print " -- Minimized" > Case SW_MAXIMIZE > Debug.Print " -- Maximized" > Case SW_RESTORE > Debug.Print " -- Restored" > End Select > End If > Case HCBT_QS > 'Debug.Print "HCBT_QS", wParam, lParam > Case HCBT_CREATEWND > 'Debug.Print "HCBT_CREATEWND", wParam, lParam > Case HCBT_DESTROYWND > 'Debug.Print "HCBT_DESTROYWND", wParam, lParam > Case HCBT_ACTIVATE > Debug.Print "HCBT_ACTIVATE", wParam, lParam > Case HCBT_CLICKSKIPPED > 'Debug.Print "HCBT_CLICKSKIPPED", wParam, lParam > Case HCBT_KEYSKIPPED > 'Debug.Print "HCBT_KEYSKIPPED", wParam, lParam > Case HCBT_SYSCOMMAND > Debug.Print "HCBT_SYSCOMMAND", wParam, lParam > Case HCBT_SETFOCUS > 'Debug.Print "HCBT_SETFOCUS", wParam, lParam > End Select > End Function > > Private Sub UserForm_Initialize() > Call SetCbtHook(Me) > End Sub > > Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) > Call UnhookCbt > End Sub > > *********************************************************** > > The only really tricky thing here is routing the execution back from the > callback into the userform. And that's not all that tricky. :-) > > I think a better general purpose design would be to create a CApplication > class that set the hook at app startup, and automatically unhooked at > shutdown. But it really depends on how you plan to be using it. > > With CBT callbacks, you're handed notificatation of what's *about* to > happen, not what just happened as with so many other hooks. In this way, > you can choose to prevent the pending event by simply returning 1 from the > CBTProc. > > http://msdn.microsoft.com/en-us/library/ms644977%28VS.85%29.aspx > > Questions? > > -- > .NET: It's About Trust! > http://vfred.mvps.org > >
From: RB Smissaert on 19 Dec 2009 08:32 OK, ignore that last e-mail. Had another go with your code and it looks all fine now. Very neat indeed and thanks for that. Just one question, don't I need CallNextHookEx as suggested in the examples in the API Guide? RBS "RB Smissaert" <bartsmissaert(a)blueyonder.co.uk> wrote in message news:OmVrZzKgKHA.1652(a)TK2MSFTNGP05.phx.gbl... > No trouble. I had a go with this, but I couldn't get it to work stable and > as I have > have a method that doesn't do call-backs and also because it looks the OP > is not > persuing this anymore I left it at that. > I have now tried you code, but again I get crashes and not sure this can > work reliably > in VBA. Let me know and I send you the .xls as I have it now. > Did you get it to work all fine in Excel, that is with the SetWindowLongA > API to change > the parent window? > > RBS > > > "Karl E. Peterson" <karl(a)exmvps.org> wrote in message > news:%23C849VEgKHA.2780(a)TK2MSFTNGP05.phx.gbl... >> RB Smissaert explained on 12/10/2009 : >>>>> Yes, best to stick to VBA stuff if you can and it looks we can. >>>>> Only minor drawback is that the OnTime resolution is is one second so >>>>> there is a small delay when minimizing and restoring Excel. >>>> >>>> Okay, interested in Plan B? >>>> >>>> Thought about a SetWindowsHookEx(WH_CBT) watching for HCBT_MINMAX? >>> >>> Yes, interested in that. >> >> Okay, sorry for taking so long to respond. Needed to find 15 minutes to >> play. (And kinda thought the hint might be enough? <bg>) Anyway, it's >> pretty basic. Here's one design. Start a new spreadsheet, and add a >> UserForm and a standard Module. Put a command button on the spreadsheet, >> and add this code behind it: >> >> *** Sheet1 ************************************************ >> >> Option Explicit >> >> Private Sub CommandButton1_Click() >> Dim f As UserForm1 >> Set f = New UserForm1 >> f.Show vbModeless >> End Sub >> >> *********************************************************** >> >> In the standard BAS module, add this code: >> >> *** Module1 *********************************************** >> >> Option Explicit >> >> Private Declare Function SetWindowsHookEx Lib "user32" Alias >> "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod >> As Long, ByVal dwThreadId As Long) As Long >> Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook >> As Long) As Long >> Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long >> >> Private m_hHook As Long >> Private m_Callback As UserForm1 >> >> Public Function SetCbtHook(obj As UserForm1) As Long >> Const WH_CBT = 5 >> m_hHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, 0&, >> GetCurrentThreadId()) >> If m_hHook Then Set m_Callback = obj >> End Function >> >> Public Function UnhookCbt() >> If m_hHook Then >> Call UnhookWindowsHookEx(m_hHook) >> Set m_Callback = Nothing >> End If >> End Function >> >> Private Function CBTProc(ByVal nCode As Long, ByVal wParam As Long, >> ByVal lParam As Long) As Long >> If Not m_Callback Is Nothing Then >> CBTProc = m_Callback.CBTProc(nCode, wParam, lParam) >> End If >> End Function >> >> Public Function WordLo(ByVal LongIn As Long) As Integer >> ' Low word retrieved by masking off high word. >> ' If low word is too large, twiddle sign bit. >> If (LongIn And &HFFFF&) > &H7FFF Then >> WordLo = (LongIn And &HFFFF&) - &H10000 >> Else >> WordLo = LongIn And &HFFFF& >> End If >> End Function >> >> *********************************************************** >> >> Finally, in the UserForm, add this code: >> >> *** UserForm1 ********************************************* >> >> Option Explicit >> >> ' CBT Hook Codes >> Private Const HCBT_MOVESIZE = 0 >> Private Const HCBT_MINMAX = 1 >> Private Const HCBT_QS = 2 >> Private Const HCBT_CREATEWND = 3 >> Private Const HCBT_DESTROYWND = 4 >> Private Const HCBT_ACTIVATE = 5 >> Private Const HCBT_CLICKSKIPPED = 6 >> Private Const HCBT_KEYSKIPPED = 7 >> Private Const HCBT_SYSCOMMAND = 8 >> Private Const HCBT_SETFOCUS = 9 >> >> Private Const SW_MAXIMIZE = 3 >> Private Const SW_MINIMIZE = 6 >> Private Const SW_RESTORE = 9 >> >> Public Function CBTProc(ByVal nCode As Long, ByVal wParam As Long, >> ByVal lParam As Long) As Long >> Select Case nCode >> Case HCBT_MOVESIZE >> Debug.Print "HCBT_MOVESIZE", wParam, lParam >> Case HCBT_MINMAX >> Debug.Print "HCBT_MINMAX", wParam, lParam >> If wParam = Application.Hwnd Then >> Select Case WordLo(lParam) >> Case SW_MINIMIZE >> Debug.Print " -- Minimized" >> Case SW_MAXIMIZE >> Debug.Print " -- Maximized" >> Case SW_RESTORE >> Debug.Print " -- Restored" >> End Select >> End If >> Case HCBT_QS >> 'Debug.Print "HCBT_QS", wParam, lParam >> Case HCBT_CREATEWND >> 'Debug.Print "HCBT_CREATEWND", wParam, lParam >> Case HCBT_DESTROYWND >> 'Debug.Print "HCBT_DESTROYWND", wParam, lParam >> Case HCBT_ACTIVATE >> Debug.Print "HCBT_ACTIVATE", wParam, lParam >> Case HCBT_CLICKSKIPPED >> 'Debug.Print "HCBT_CLICKSKIPPED", wParam, lParam >> Case HCBT_KEYSKIPPED >> 'Debug.Print "HCBT_KEYSKIPPED", wParam, lParam >> Case HCBT_SYSCOMMAND >> Debug.Print "HCBT_SYSCOMMAND", wParam, lParam >> Case HCBT_SETFOCUS >> 'Debug.Print "HCBT_SETFOCUS", wParam, lParam >> End Select >> End Function >> >> Private Sub UserForm_Initialize() >> Call SetCbtHook(Me) >> End Sub >> >> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As >> Integer) >> Call UnhookCbt >> End Sub >> >> *********************************************************** >> >> The only really tricky thing here is routing the execution back from the >> callback into the userform. And that's not all that tricky. :-) >> >> I think a better general purpose design would be to create a CApplication >> class that set the hook at app startup, and automatically unhooked at >> shutdown. But it really depends on how you plan to be using it. >> >> With CBT callbacks, you're handed notificatation of what's *about* to >> happen, not what just happened as with so many other hooks. In this way, >> you can choose to prevent the pending event by simply returning 1 from >> the CBTProc. >> >> http://msdn.microsoft.com/en-us/library/ms644977%28VS.85%29.aspx >> >> Questions? >> >> -- >> .NET: It's About Trust! >> http://vfred.mvps.org >> >> >
From: Peter T on 21 Dec 2009 07:04 "Karl E. Peterson" <karl(a)exmvps.org> wrote in message news:%< < CBT hook to catch Excel window events snipped > > > The only really tricky thing here is routing the execution back from the > callback into the userform. And that's not all that tricky. :-) > > I think a better general purpose design would be to create a CApplication > class that set the hook at app startup, and automatically unhooked at > shutdown. But it really depends on how you plan to be using it. > > With CBT callbacks, you're handed notificatation of what's *about* to > happen, not what just happened as with so many other hooks. In this way, > you can choose to prevent the pending event by simply returning 1 from the > CBTProc. > > http://msdn.microsoft.com/en-us/library/ms644977%28VS.85%29.aspx > > Questions? Very interesting, and the link too I'm looking to see: does it work, is it safe, does it hog recourses Well it certainly works:-) Safe, I think that depends - I appreciate this hook is safer than conventional subclass & callback. Even so, if the code is in plain view the user could crash Excel (say) by adding a new procedure to the normal module. In practice probably most wouldn't edit the module with the form showing and hook running, but some would (I crashed Excel doing just that). However if hidden in a password locked project that the user has no business looking at, I guess all would be fine. Unless, and maybe you could clarify - I noticed if I Excel closes unexpectedly and the UnhookCbt not called, next time Excel starts with the same file, the hook is still running and no way to stop it. Resources: There's no timer and I notice the VBE's caption flickering with the hook running. So I assumed the loop is continuously hogging resources. I was surprised though that it didn't seem to impact much on the timing of a test loop (maybe about 10%). So I put a counter in the callback, seems like it's triggered about 10-50 times/second, strangely variable but even so not that much in the way of resources. Is that also your view, particularly in a VBA environment? Other observations: I was curious about this comment of yours which indeed is backed up in the MSDN link > With CBT callbacks, you're handed notificatation of what's *about* to > happen, not what just happened as with so many other hooks. IOW, a kind of 'Before_WindowAction' event. I mentioned previously the purpose (OP's objective) for the 'event' to be able to do the following depending on the window state - If Excel is minimized H = 0 Else H = xlApp.Hwnd SetWindowLongA userform-hWnd, GWL_HWNDPARENT, H With my previous implementation also had to hide/re-show the form as a kludge to 'effect' the change. However with this pseudo 'before-event' the hide/re-show no longer necessary! At least I assume that's the reason, does that make sense? > The only really tricky thing here is routing the execution back from the > callback into the userform. And that's not all that tricky. :-) (Snipped -) you have it arranged to for the callback to call a similarly named procedure in the form. It could be like that but I don't see why the entire do-stuff can't also be in the actual CBTProc callback function (or in another function in the normal module), or indeed in a "CApplication class" as you suggest. (in your other recent post to me) > I'll be curious what you guys think of using the CBT hook for these > purposes. I certainly think it has possibilities, even in VBA. There are all sorts of reasons for wanting Excel's resize event; though perhaps not as a solution for the OP's particular objective (the maximum one second delay using VBA's simple built in OnTime method is fine). There are also other events I'd like to know about and this might be the way. One thing I've wanted to be able to do for years is to know when user clicks a scrollbar in an Excel workbook. That changes the window's 'VisibleRange' but without triggering any event exposed to Excel. I have tried subclassing in the past (and that c-dll approach), got results but with the expected downsides of being risky and resource intensive. Many thanks Karl for posting this CBT hook demo. It's certainly extended my limited knowledge of what's possible, although not yet my full understanding which is quite another matter <g> Regards, Peter T
From: Peter T on 21 Dec 2009 07:04
Many thanks for your comments. See my fuller reply to your other recent post to RBS Regards, Peter T |