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 |
I usually use the Formatting options from the VBA menu.
Format > Horizontal Spacing > Make Equal
Format > Group
Format > Center in Form > Horizontally
Format > Ungroup
Buttons should be aligned to the right, not centred.
Rob says:
January 11, 2011 at 2:53 pm
Buttons should be aligned to the right, not centred.
——————————————
Excuse the question, but why?
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.