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.

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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>