Advanced controls in userforms with VBA macros
Part seven of a nine-part series of blogs

The two previous parts of this mini-blog have shown how to draw forms and how to write code to handle form events. This final part shows how to add some of the more exotic controls to user forms, like combo boxes, list boxes, multipage controls, spinners and option buttons.

  1. Advanced Controls - Our Example
  2. Multipage Controls
  3. Combo boxes (and list boxes)
  4. Multiple Column Combo Boxes and Listboxes
  5. Multi-select ListBoxes
  6. SpinButtons (Spinners)
  7. Check Boxes (this blog)
  8. Option Buttons (Radio Buttons)
  9. The Calendar Control

This blog is part of our Excel VBA tutorial.  Wise Owl's main business is running Excel, VBA and other courses for businesses.

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.

Check Boxes

A check box can have two possible values: True or False.

Check box

You either do want a biscuit, or you don't!

 

Adding a CheckBox

To add a checkbox to a form, use the CheckBox tool:

The checkbox tool

Click on the CheckBox tool to add it to a form.

 

Formatting a CheckBox

A checkbox comes with associated text, which can appear either to the right or left of the box itself:

The Alignment property of a checkbox

The Alignment property doesn't align the text within its container, but instead specified whether it should appear to the right or left of the tick box.

 

To show how this works, consider a check box with the following properties:

Property Value
Alignment 0 - frmAlignmentLeft
TextAlign 1 - frmTextAlignRight

Here's what this would look like:

A checkbox with right alignment

The text appears to the right of the tick box, but is left aligned.

 

Picking Up on a Checkbox's Value

The Value property of a checkbox tells you whether it has been ticked or not:

'put biscuit choice in column 6 of current block

ActiveCell.Offset(0, 5).Value = IIf(Me.chkBiscuit, "Yes", "No")

This is short-hand for:

'put biscuit choice in column 6 of current block

If Me.chkBiscuit = True Then

ActiveCell.Offset(0, 5).Value = "Yes"

Else

ActiveCell.Offset(0, 5).Value = "No"

End If

If you set a checkbox's Tristate property to True (not the default) it can - as the name suggests - have 3 possible values, according to whether it is ticked, unticked or left null (in which case it would appear shaded).  I can't help feeling you'd be better off with a combobox instead, however!

This blog has 0 threads Add post