Detect Excel application minimize/restore?
in [Visual Basic]
|
From: RB Smissaert on 10 Dec 2009 18:43 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. RBS "Karl E. Peterson" <karl(a)exmvps.org> wrote in message news:euR4KAfeKHA.4952(a)TK2MSFTNGP06.phx.gbl... > 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!] > >
From: Karl E. Peterson on 10 Dec 2009 19:00 RB Smissaert formulated the question : > 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? -- [.NET: It's About Trust!]
From: RB Smissaert on 11 Dec 2009 02:36 Yes, interested in that. RBS "Karl E. Peterson" <karl(a)exmvps.org> wrote in message news:ey3SqTfeKHA.2460(a)TK2MSFTNGP04.phx.gbl... > RB Smissaert formulated the question : >> 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? > > -- > [.NET: It's About Trust!] > >
From: bart.smissaert on 11 Dec 2009 12:51 No problem and it worked all fine on the home machine, but then on another machine it was no good at all, making it impossible to restore Excel by clicking the toolbar icon. Somehow the recursive OnTime must have been messing matter up. I think somehow this is not good idea and hooking into to Excel window message handler (dealing with the MinMax) is problably the way to do this. RBS > However I > think the window handling side of things is not quite right, I'm sure RB > won't mind me saying :-) > > Regards, > Peter T
From: Karl E. Peterson on 18 Dec 2009 19:52
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 |