Kamis, 27 Januari 2011

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:































































































































GHI
3


4
Form width411
5


6
Control width35
7


8
Number of controls3
9


10
Inter-control gap17.5
11
Space occupied by all controls140
12
Empty space in form271
13
Empty space on left135.5
14


15Left positionControl 1135.5
16
Control 2188
17
Control 3240.5
18


19


20


21







Formulas:





























































































































GHI
3


4
Form width411
5


6
Control width35
7


8
Number of controls3
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


15Left 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





Tidak ada komentar: