A Resizable UserForm

Excel uses several resizable dialog boxes. For example, the Name Manager dialog box can be resized by clicking and dragging the bottom-right corner.

If you would like to create a resizable UserForm, you'll quickly discover that there's no direct way to do it. One solution is to resort to Windows API calls. That method works, but it's complicated to set up. In this section, I present a much simpler technique for creating a user -resizable UserForm.

Note  

Credit for this technique goes to Andy Pope, an Excel expert and Microsoft MVP who lives in the UK. Andy is one of the most creative Excel developers I've ever met. For a real treat (and lots of interesting downloads), visit his Web site at http://www.andypope. info .

Figure 15-18 shows the UserForm that's described in this section. It contains a ListBox control that displays data from a worksheet. Notice the scrollbars on the ListBox . That means there's a lot more information that doesn't fit. Also, notice the bottom-right corner of the dialog box. It displays a (perhaps) familiar sizing control.

Figure 15-18: This is a resizable UserForm.

Figure 15-19 shows the same UserForm after being resized by the user. Notice that the size of the ListBox is also increased, and the Close button remains in the same relative position. You can stretch this UserForm to the limits of your monitor. A UserForm can have a maximum width and height of 12,287.25 units. The minimum height is 24.75 units (equal to the height of the title bar), and the minimum width is 105 units.

Figure 15-19: The UserForm after being increased in size.

CD-ROM  

This example is available on the companion CD-ROM. The filename is resizable user-form.xlsm .

The trick here involves a Label control, which is added to the UserForm at runtime. The sizing control at the bottom-right corner is actually a Label control that displays a single character: The letter o (character 111) from the Marlett font, character set 2. This control (named objResizer ) is added to the UserForm in the UserForm_Initialize procedure:

 Private Sub UserForm_Initialize() '   Add a resizing control to bottom right corner of UserForm     Set objResizer = Me.Controls.Add("Forms.label.1", MResizer, True)     With objResizer         .Caption = Chr(111)         .Font.Name = "Marlett"         .Font.Charset = 2         .Font.Size = 14         .BackStyle = fmBackStyleTransparent         .AutoSize = True         .ForeColor = RGB(100, 100, 100)         .MousePointer = fmMousePointerSizeNWSE         .ZOrder         .Top = Me.InsideHeight - .Height         .Left = Me.InsideWidth - .Width     End With End Sub 

Note  

Although the Label control is added at runtime, the event-handler code for the object is contained in the module. Including code for an object that doesn't exist does not present a problem.

This technique relies on these facts:

• The user can move a control on a UserForm (see "A UserForm with Movable Controls," earlier in this chapter).

• Events exist that can identify mouse movements and pointer coordinates. Specifically, these events are MouseDown and MouseMove .

• VBA code can change the size of a UserForm at runtime, but a user cannot.

Do a bit of creative thinking about these facts, and you see that it's possible to translate the user's movement of a Label control into information that can be used to resize a UserForm.

When the user clicks the objResizer Label object, the objResizer_MouseDown event- handler procedure is executed:

 Private Sub objResizer_MouseDown(ByVal Button As Integer, _     ByVal Shift As Integer, ByVal  As Single, ByVal Y As Single)     If Button = 1 Then         LeftResizePos = x         TopResizePos = Y     End If End Sub 

This procedure executes only if the left mouse button is pressed (that is, the Button argument is 1) and the cursor is on the objResizer label. The — and Y mouse coordinates at the time of the button click are stored in module-level variables : LeftResizePos and TopResizePos .

Subsequent mouse movements fire the MouseMove event, and the objResizer_ MouseMove event handler kicks into action. Here's an initial take on this procedure:

 Private Sub objResizer_MouseMove(ByVal Button As Integer, _     ByVal Shift As Integer, ByVal  As Single, ByVal Y As Single)     If Button = 1 Then         With objResizer             .Move .Left +  - LeftResizePos, .Top + Y - TopResizePos             Me.Width = Me.Width +  - LeftResizePos             Me.Height = Me.Height + Y - TopResizePos             .Left = Me.InsideWidth - .Width             .Top = Me.InsideHeight - .Height         End With End Sub 

If you study the code, you'll see that the UserForm's Width and Height properties are adjusted, based on the movement of the objResizer Label control. Figure 15-20 shows how the UserForm looks after the user moves the Label control down and to the right.

Figure 15-20: VBA code converts Label control movements into new Width and Height properties for the UserForm.

The problem, of course, is that the other controls in the UserForm don't respond to the UserForm's new size. The ListBox should be expanded, and the CommandButton should be relocated so it remains in the lower-left corner.

More VBA code is needed to adjust the controls in the UserForm when the UserForm size is changed. The location for this new code is in the objResizer_MouseMove event handler procedure. The statements that follow do the job:

 '   Adjust the ListBox     On Error Resume Next     With ListBox1         .Width = Me.Width - 22         .Height = Me.Height - 100     End With     On Error GoTo 0 '   Adjust the Close Button     With CloseButton         .Left = Me.Width - 70         .Top = Me.Height - 54     End With 

These two controls are adjusted relative to the UserForm's size (that is, Me ). After adding this new code, the dialog box works like a charm . The user can make it as large as needed, and the controls adjust.

It should be clear that the most challenging part of creating a resizable dialog box is figuring out how to adjust the controls. When you have more than two or three controls, things can get very complicated.