Distributing controls in a userform

On many occasions there is a need to lay out controls horizontally in a userform so that they are equally spaced and, as a group, centered within the userform. An example is the group of three buttons (OK, Cancel, and Help) in the userform below.

position-calculate-example

The worksheet below calculates the left position of each of the controls. Key assumptions: 1) each control is the same width; 2) the space between two controls is half the width of the control; 3) the layout is rational and logical (i.e., the worksheet has no GIGO protection); and, 4) there are no more than 6 controls.

Values:

  G H I
3
4 Form width 411
5
6 Control width 35
7
8 Number of controls 3
9
10 Inter-control gap 17.5
11 Space occupied by all controls 140
12 Empty space in form 271
13 Empty space on left 135.5
14
15 Left position Control 1 135.5
16 Control 2 188
17 Control 3 240.5
18
19
20
21

Formulas:

  G H I
3
4 Form width 411
5
6 Control width 35
7
8 Number of controls 3
9
10 Inter-control gap =I6/2
11 Space occupied by all controls =I6*I8+I10*(I8-1)
12 Empty space in form =I4-I11
13 Empty space on left =I12/2
14
15 Left position =IF(ROW()-ROW($H$15)<$I$8,”Control
“&(ROW()-ROW($H$15)+1),””)
=IF(H15<>””,I13,””)
16 =IF(ROW()-ROW($H$15)<$I$8,”Control
“&(ROW()-ROW($H$15)+1),””)
=IF(H16<>””,I15+$I$6+$I$10,””)
17 =IF(ROW()-ROW($H$15)<$I$8,”Control
“&(ROW()-ROW($H$15)+1),””)
=IF(H17<>””,I16+$I$6+$I$10,””)
18 =IF(ROW()-ROW($H$15)<$I$8,”Control
“&(ROW()-ROW($H$15)+1),””)
=IF(H18<>””,I17+$I$6+$I$10,””)
19 =IF(ROW()-ROW($H$15)<$I$8,”Control
“&(ROW()-ROW($H$15)+1),””)
=IF(H19<>””,I18+$I$6+$I$10,””)
20 =IF(ROW()-ROW($H$15)<$I$8,”Control
“&(ROW()-ROW($H$15)+1),””)
=IF(H20<>””,I19+$I$6+$I$10,””)
21
Posted in Uncategorized

4 thoughts on “Distributing controls in a userform

  1. I usually use the Formatting options from the VBA menu.

    Format > Horizontal Spacing > Make Equal
    Format > Group
    Format > Center in Form > Horizontally
    Format > Ungroup

  2. Rob says:
    January 11, 2011 at 2:53 pm

    Buttons should be aligned to the right, not centred.

    ——————————————
    Excuse the question, but why?

  3. I think Rob is coming from the angle that it’s not typically what you’d see in a Microsoft app.
    Microsoft have UI design guidelines: http://msdn.microsoft.com/en-us/library/aa511331.aspx

    But, I think the real question is, how will this impact the user experience?

    There might be some video clips (youtube maybe?) of Lou Carbone, who changed the way I think about building UI.

Leave a Reply

Your email address will not be published. Required fields are marked *