Resizeable VBA UserForms (and Unicode Form Captions)

[MountainMan]

If you have done much VBA programming you know that it is frustrating to use UserForms on PC’s that have different display resolutions and multiple monitors. In addition, although the VBA Forms package will display Unicode characters, you can’t enter them in the forms editor so it is difficult to get them to display.

This easy-to-use system takes care of all of that. There is a small amount of code that needs to be added to each form you want to be resizable and there is a Class module to include with your document. (I generally don’t like Class modules because they are clumsy to use but in situations where we have some code that can be re-used a lot then the hassle of setting them up and using them outweighs their complexity and since you may want to apply the re-sizing capability to many forms this seemed like a good candidate to use a class module.) One of the strange things about VBA forms compared to VB forms is that you can only change the form size and location from within the code for the form. It would have been nice to be able to access the form position data from within the class module and then make the changes to the form that we want to re-size the form but this isn’t possible. I put as much of the code as possible in the class module but some had to go

The system works for all 32 and 64-bit Windows VBA.

BTW, I believe strongly in commenting code. One of the negatives about VBA code is that the comments drag around with each document so I will do most of the commenting in this document which is your main reference for resizable forms.


Class Module

There is a class module called clResizer that needs to be imported into your project. In the VBA IDE press Ctrl-M (or do File | Insert File) and find this file. There is nothing you need to do to this file beyond importing it.

Code for Each Resizable Form

In a form that you want to be resizable you should put the following code (cut and paste if necessary):

In the declaration section which is below the Option Explicit statement (which you definitely should be using):

Private frmResize As New clResizer
Public UniCaption As String
Private Executing As Boolean
Private Activated As Boolean

Below this and any other declarations you may have for your form, put the following code:

Private Sub UserForm_Resize()
Dim NewHeight As Single, NewZoom As Integer
If Not Executing Then
   If frmResize.FormResized(Me.Left, Me.Top, Me.Width, Me.Height, NewHeight, NewZoom) Then
      Executing = True
      Me.Height = NewHeight
      Me.Zoom = NewZoom
      Executing = False
      End If
   End If
End Sub
Private Sub FormSize(Left As Single, Top As Single, Width As Single, Height As Single)
Executing = True
Me.Left = Left: Me.Top = Top: Me.Width = Width: Me.Height = Height
Executing = False
UserForm_Resize
End Sub
 

 
Public Sub CenterForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
Dim L As Single, T As Single, W As Single, H As Single
frmResize.CFCalc WidthPerCent, HeightPerCent, Limit2Screen, L, T, W, H
FormSize L, T, W, H
End Sub
 

 
Public Sub MoveForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)
Dim L As Single, T As Single
frmResize.MoveForm WidthPerCent, HeightPerCent, Limit2Screen, L, T, Me.Width, Me.Height
Me.Move L, T
End Sub

Then either include the following routines or take the code and put it in your versions of the same routines:

Private Sub UserForm_Initialize()
frmResize.NewForm Me, Me.Left, Me.Top, Me.Width, Me.Height, Me.Caption, Me.Zoom
End Sub
 

 
Private Sub UserForm_Activate()
If Not Activated Then
   ' dipslay the Unicode caption if not blank and makes form resizable
   Call frmResize.Activate(UniCaption)
   CenterForm 60, 60, True ' make the form fill up 60% of the screen
 
   Activated = True
   End If
End Sub

That’s all there is to setting this up. When you show this Userform it will be resizable and as you change the size of the form by using the standard Windows technique of mouse-dragging the corners or sides the form will change in size and all of the controls in the form will also change size (that’s the best part!).

From the Activate code above you can also see that the default is to center the form on the screen and have the form be 60% of the size of the screen. You can change this to whatever you want and you can have it start up anywhere on the screen including wherever it was the last time the form was used.


Displaying a Unicode Caption

It is frustrating trying to display a Unicode caption in the form title especially since VBA and Excel and Word etc. easily display Unicode characters. I have created a public variable in the form call UniCaption. If you set this variable to any string then when the form is displayed UniCaption will be the form caption instead of whatever was used previously as the caption.

Note – It is very important that your form has at least some caption set in the editor. I use the form caption to ‘find” the handle to it inside of Windows. To be safe just make its value be something unique from your other VBA forms that potentially could be loaded at the same time. This normally isn’t a problem since most userforms in VBA are modal which means nothing else happens until that form goes away.

Suppose you have a form named fmTestMe. Suppose you want the string “あいうえお ovat japanilaisia hiragana-merkkej.” to be displayed as the caption of fmTestMe. If it never changed you could put the following line in the Activate sub of the form:

UniCaption = ChrW$(&H3042) & ChrW$(&H3044) & ChrW$(&H3046) & ChrW$(&H3048) & _
ChrW$(&H304A) & " ovat japanilaisia hiragana-merkkej."

But suppose you wanted to have this value change over time (maybe a customer name or something) then you could easily reference a cell if you were using Excel. Suppose you had cell B20 designated to hold the text for the caption then you could have a line somewhere in your code before you do the show:

fmTestMe.UniCaption = Sheets("Sheet1").Range("B20").Value
fmTestMe.Show

Setting the Form’s Initial Position

Below are some techniques for setting the initial position of the form. My recommendation is to initially put the form on the same screen as the VBA host (Excel, Word, etc.) and since it is resizable and moveable the user will put it wherever it works best and then we will save and restore that size and position for future re-use.

Because of the way forms work, once it is displayed the programmer has little control over the position of the form. Programatically we can respond to the Resize event (which we already do) but that is largely driven by the user who is resizing the form. I suppose you could catch this re-sizing event and do something different but I don’t know what. There is no easy way to catch a Move event and the whole purpose of this system is to let the user move the form and re-size as he/she sees fit. So this means that in general we would want to move and or re-size the form just before it is being displayed.

You can put code in the Initialize event for the form but keep in mind that at this point we have not yet had Windows make the form resizable so any attempt to resize the form will not work. Also, if you try domething like the following it won’t work either:

fmTestMe.CenterForm(50, 50, True)
fmTestMe.Show

Anything in a normal module before the Show command basically causes the Initialize event to fire and our code will be executing before the Windows call to enable resizing. The resizing code is called in the Activation event which is after Initialize and just before the form is displayed.

The only way I know of to get code to affect the form after the Show statement is if the form has been hidden instead of unloaded.

My recommendation is to decide what you want to do regarding the form size and location and put the code to do this in the Activate event procedure in the code for the Form. You have two routines in the Form code for form location that enable you set the size and position to be centered or anywhere on the screen and with little effort you can derive many others.

Sub CenterForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)

This enables you to center and optionally resize the form.

• To size the form based on the available screen width and height
  
  • WidthPerCent and HeightPerCent are the %'s of the screen width and height respectively
  • To make a form fill up half of the screen regardless of the screen size and resolution you would specify the following in the form’s Activate procedure:
  
  

CenterForm 50, 50, True

• Note – As long as Zoomable is True (default), the setting for HeightPerCent is ignored because the code determines the required height to keep the height/width ratio constant.

• To size the form based on the original size of the form
  
  • WidthPerCent and HeightPerCent are based on the original form size but negative
  • To make a form be twice the size of the original form you would specify the following in the Activate procedure of your form:
  
  

CenterForm -200, -200, True  ' for 200% but negative

• Note – As long as Zoomable is True, the HeightPerCent parameter is ignored.

• If limit2Screen is True then the form size is adjusted as necessary to keep it all onscreen.

Sub MoveForm(WidthPerCent As Single, HeightPerCent As Single, Limit2Screen As Boolean)

This enables you to move the form and optionally keep it onscreen.

• To move the form based on the available screen width and height
  
  • WidthPerCent and HeightPerCent are the %'s of the screen width and height respectively
  • To make a form’s upper left corner go to the middle of the screen regardless of the screen size and resolution you would specify the following in the form’s Activate procedure:
  
  

MoveForm 50, 50, True

• Note – As long as Zoomable is True (default), the setting for HeightPerCent is ignored because the code determines the required height to keep the height/width ratio constant.

• To move the form to the specific left and top coordinates
  
  • WidthPerCent and HeightPerCent are the specific form position values for Left and Top
  • To make a form go to the top left of the screen you would specify this in the Activate procedure:
  
  

MoveForm 0, 0, True

• Note – As long as Zoomable is True, the HeightPerCent parameter is ignored.

• If limit2Screen is True then the form size is adjusted as necessary to keep it all onscreen.

Form Aspect Ratio

If a form is resized, it is normally desirable to have eerything on the form change size with the form itself. When this happens we all want the ratio of the form height to width to stay the same so that we don’t get strange looking text or graphics. I make use of a VBA form parameter called Zoom to manage this but in order for it to work well we need to keep the aspect ratio the same. When you re-size a form I have the code continuously check this ratio and keep it the same. Run the sample and drag the lower right of the form to change size and notice that it won’t allow you to make the form tall and skinny or short and fat. It also won’t let you change size by dragging the sides of the form (that is allowed in Windows but I don’t let it happen because if you try to change the height or width without the other since that would change the height/width ratio. You can only change the form size by dragging one of the corners.


Zooming

Resizing a form is generally not of much use if all of the controls on the form do not change. I use a VBA Forms parameter called Zoom to resize form components to fit the form as it is re-sized. When it is re-sized either by your code or by the user dragging a corner of the form, everything on the form looks the same except it is larger or smaller like the re-sized form.

There may be an odd time or two when you want to change the form size but not use the Zoom feature. For example you could have some controls that start out below the bottom of the displayed form so that the user can’t see them but then later the bottom of the form could be dropped to expose the additional controls. For this possibility we maintain a Boolean variable in the class module called Zoomable and if it is set to False then when the form is resized the controls don’t get resized.

In the sample project we put a check box on the example user form that lets you turn this on or off with this code:

Private Sub cboxKeepRatio_Click()
frmResize.Zoomable = CBool(cboxKeepRatio.Value)
End Sub

In the form code we refer to the class module clResize as frmResize and that class module contains the Public variable Zoomable which we toggle between True and False based on the user clicking on the check box.


Continued below...