Resize userform but limit the max size.

  • I have had some great help recently, in particular how to make a userform resizable.

    That leads on to another question - Other than checking the height and width in the resize event and resetting it in code if outside the limits, is there any other, more elegant, way of doing it?

    I have seen programs where the forms just refuse to get any bigger/smaller after a certain size without the horrible flashing of the border when the width or height is set in code on a VBA form.

  • Re: Resize userform but limit the max size.


    any other, more elegant, way

    Yes and No.

    The userform Resize event is only raised after Windows has done what it has to do when a window is resized (redraw the window based on the new size); so changing the width or height in the resize event causes the flashing you describe when Windows jumps in and redraws the userform in between times.

    The only other way is to Subclass the Window. I won't go into details, but essentially you tell Windows that one of your procedures is to be called before the default WindowProc for an object.

    This particular procedure watches for a particular message (WM_GETMINMAXINFO). Once the message is received, the procedure tells Windows what the Min and Max sizes should be. The message is then passed back and Windows continues calling the default WindowProc but it now uses the user defined sizes rather than the default.

    All of this happens on a much lower level than the Resize event and before Windows actually does anything with the window. The end effect is it will only allow the window to be sized between the defined Min and Max dimensions.

    While it can be done, Subclassing has problems in VBA. If, for example, you are subclassing a Window and you press the Stop button on the toolbar in the VBE Dev environment, then Excel crashes; if you pause the code and try to single step while the hook is active then Excel will probably crash - if you single step the inserted procedure then Excel will crash; if there's any error in the procedure, Excel crashes even if there's a defined error handler.

    Other than those major issues, there are 2 other smaller problems when sizing windows manually - the userform cannot be modeless and cannot (or should not) have a Max button.

    If you subclass a modeless userform to watch for resize messages then the code goes into a endless loop in the user defined window procedure.

    If you click the Max button then the userform will size to it's max allowed height and width, but as Windows knows it is to be maximised, it moves it to the top left of the screen - of course, you could always subclass the WM_MAXIMISE message and cancel it, but it's simpler to just remove the Max button.

    All in all, subclassing might be useful in very limited circumstances but is probably more trouble than what it's worth.

    Sample attached. Select the Checkbox to start subclassing. Some textboxes will be shown to allow the min/max sizes to be defined, you can change these at any time and then resize to the new limits. This also has a 'Lock Position' option which prevents a userform from being moved. As mentioned earlier, "The message is then passed back...", to lock the position the message is simply not passed back so Windows takes no further action. This overrides the resizable border setting, if enabled.

    You use this at your own risk - the potential problems have been described.

    Some reading:
    About Messages and Message Queues (MS)
    The Basics of Subclassing and Hooks