Window Change Event

Question

Problem

Call the procedure when the Excel main window changes.

First try:

Sub Workbook_WindowResize(ByVal Wn As Window) Debug.Print Wn.Width & "x" & Wn.Height End Sub 

Results:

The subroutine is called whenever the inner window of the workbook changes, but not when the size of the application window changes.

Second attempt

 Dim WithEvents App As Application Private Sub App_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window) Debug.Print Wn.Width & "x" & Wn.Height End Sub 

Results:

Oddly enough, the same thing happens that happened before, which definitely surprised me. The event occurs only when the window size of the workbook is changed instead of the application window.

For this reason, I began to study the use of the window API.

There are many examples of installing SystemWide keyboard and mouse hooks using the window APIs. This happens in the same directions:

 Public Enum enHookTypes WH_CALLWNDPROC = 4 WH_CALLWNDPROCRET = 12 WH_CBT = 5 WH_DEBUG = 9 WH_FOREGROUNDIDLE = 11 WH_GETMESSAGE = 3 WH_HARDWARE = 8 WH_JOURNALPLAYBACK = 1 WH_JOURNALRECORD = 0 WH_MOUSE = 7 WH_MSGFILTER = (-1) WH_SHELL = 10 WH_SYSMSGFILTER = 6 WH_KEYBOARD_LL = 13 WH_MOUSE_LL = 14 WH_KEYBOARD = 2 End Enum Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As LongPtr, ByVal lpfn As Long, ByVal hMod As Long, ByVal dwThreadId As Long) As LongPtr Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long Private Declare Function GetLastError Lib "kernel32" () As Long 'Ensure that your hook procedure does not interfere with the normal operation of other hook procedures Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long Public hndl As Long Sub HookWindow() hndl = SetWindowsHookEx(WH_CALLWNDPROC, AddressOf measureWindow, Application.Hinstance, 0&) Debug.Print hndl & "~~" & GetLastError() End Sub Sub unhookWindow() ret = UnhookWindowsHookEx(hndl) Debug.Print ret End Sub Public Sub measureWindow(code As Long, wParam As Long, lParam As Long) If code > 0 Then Debug.Print ThisWorkbook.Windows(1).Width & "x" & ThisWorkbook.Windows(1).Height Else ret = CallNextHookEx(measureWindow, code, wParam, lParam) End If End Sub 

Results:

If I replaced WH_CALLWNDPROC with:

 hndl = SetWindowsHookEx(WH_CALLWNDPROC, AddressOf measureWindow, Application.Hinstance, 0&) 

with WH_KEYBOARD_LL subroutine is called whenever a key is pressed. Similarly, if I replace it with WH_MOUSE_LL , the subroutine is called whenever the mouse moves or the mouse button is pressed.

The problem is that when I try to connect a routine to WH_CALLWNDPROC , nothing happens?

Why?

I'm still not sure, but the same is true for all ENUMS in enHookTypes except WH_MOUSE_LL and WH_KEYBOARD_LL . After reviewing the WinAPI documentation, I read that you can use GetLastError from Kernel32.dll to find out why the operation failed.

The error numbers that I have received so far (in decimal form) are error 5 (for MAGAZINE hooks) and error 1428 for the rest.

In the end, this also failed.

Answer 1

Application.Windows is a collection of Worbooks window objects open in the application. The WindowResize event occurs when the window is not maximized and resized. Workbook_WindowResize (ByVal Wn As Window) is displayed inside the workbook object itself. The Application_WindowResize event (ByVal Wb as Workbook, ByVal Wn As Window) is associated with ANY / ALL books in the application when the window without resizing the workbook resizes. Consequently, the difference is in the links transmitted by the events. This is only a window in the first case, from the book that raised the event, inside the object of the workbook, and there is no question what window it is in (this is the window of the book "I"). This is both a workbook and a workbook window when it rises at the application level, since the workbook refers to the identification of needs. And no, Excel does not have a “Resize” event for the application window itself, and you will need to go to the API for this.

In later versions of Excel (last 2010), the Excel application window has one workbook, the workbook window is always maximized in the old sense, and both work and application events refer to the same workbook and will work just like and you would like them to.

Answer 2

The solution is to create a timer event that checks and compares the width every few seconds ...

 Sub my_ONTIME() application.OnTime Now + TimeValue("00:00:2"), "my_TIMEREVENT" End Sub Sub my_TIMEREVENT() If application.Width <> EWIDTHR Then ESCREENRESIZE my_ONTIME End Sub Sub ESCREENRESIZE() Dim EWIDTH As Single Dim ESIDE As Single Dim EMID As Single EWIDTH = application.Width EWIDTHR = EWIDTH If EWIDTH < 500 Then EWIDTH = 500 application.Width = 500 End If EMID = 80 * 5.41 ESIDE = ((EWIDTH - EMID) / 2) / 5.41 Sheet1.Columns("A:A").ColumnWidth = ESIDE Sheet1.Columns("C:C").ColumnWidth = ESIDE End Sub