Drawing UserForms in the VBA code editor
Part six of a seven-part series of blogs

Learn how to create your own custom dialog boxes in VBA, using UserForms. This is the first part of a three-part series (the other parts are on Writing Code for UserForms and Creating Advanced Controls).

  1. An Introduction to UserForms in VBA
  2. Creating and Using UserForms in VBA
  3. The Properties Window
  4. Selecting Forms and Controls
  5. Formatting Forms and Controls
  6. Grouping, Aligning, Sizing, Spacing and Arranging (this blog)
  7. Controlling How Form Keys Work

This blog is part of our Excel VBA tutorial series.  We also run training classes in Excel and courses in VBA.

Posted by Andy Brown on 28 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Grouping, Aligning, Sizing, Spacing and Arranging

An earlier part of this blog showed how to select multiple controls at the same time, and explained that the one with the white borders is the dominant control (and what this means).  This webpage shows how to align, distribute, center and otherwise arrange controls, and also how to group them together.

Aligning Controls to Each Other

To align controls, select them and choose the right-mouse button options shown below:

Three controls selected, with align menu

Here the Name: label is the dominant control, so selecting Lefts as shown will align the selected controls to its left edge.

 

Equally Spacing Controls Horizontally or Vertically

In the form below, the controls need distributing equally:

Controls with unequal vertical spacing

The control would look better equally spaced in this case.

 

To do this, select the controls as shown and then choose one of the menu options shown here:

The Vertical Spacing menu option

The option shown is the one we want in this instance, but it's reasonably obvious what the others would do.

 

Note that in this case you would then have to do the same thing to the textboxes/combobox, to get them to align in the same way.

Making Controls the Same Size as Each Other

Here's how to get 2 or more controls to have the same size or width:

Size Width menu option

Select the controls, then right-click on one of them and choose the menu option shown here.  The Drink combobox is the dominant control, so the other two selected controls will be resized to have the same width as this one.

 

Grouping Controls

When you've drawn part of a form, it's often useful to group its controls together so that they behave as a single control:

Several controls in a frame

Here we want to group all of the controls in the frame together so that we can move them around as one.

 

One way to do this is to select all of the controls and group them together:

Controls selected for grouping Controls grouped together
Group the controls like this ... ... and they'll be selected as one.

Even after grouping controls together, you can still format individual controls within the group separately, so you get the best of both worlds.

You can ungroup grouped controls in the same way:

The Ungroup menu

Right-click on a grouped set of controls and choose to ungroup it as shown here.

 

Using Frames to Group Controls

An alternative way to group controls together is by creating a frame to hold them.  First cut any controls you want to appear on the frame:

Cut menu for selected controls

Select any controls you want to group together, and cut them to the clipboard.

 

Click on the Frame tool, and use it to draw a rectangle:

The Frame tool

Click on this tool, and draw a rectangle for your frame.

 

You can now delete the title of the frame, and paste your controls on to it:

Frame title

Delete the Frame1 title (it's the Caption property of the control) to make it look tidier, then paste the controls from the clipboard onto the frame (make sure you have it selected when you do this).

 

Result: wherever the frame goes, so too do any controls sitting on it:

A selected frame

When you move the frame, the controls on it move too.

 

You could always set the BorderStyle of the frame to frmBorderStyleNone to hide its border, if you don't want to see the rectangle.

 

This blog has 0 threads Add post