- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
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.
- Advanced Controls - Our Example
- Multipage Controls
- Combo boxes (and list boxes)
- Multiple Column Combo Boxes and Listboxes
- Multi-select ListBoxes
- SpinButtons (Spinners)
- Check Boxes (this article)
- Option Buttons (Radio Buttons)
- 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.
Check Boxes
A check box can have two possible values: True or False.
You either do want a biscuit, or you don't!
Adding a CheckBox
To add a checkbox to a form, use 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 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:
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!
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.
- Advanced Controls - Our Example
- Multipage Controls
- Combo boxes (and list boxes)
- Multiple Column Combo Boxes and Listboxes
- Multi-select ListBoxes
- SpinButtons (Spinners)
- Check Boxes (this article)
- Option Buttons (Radio Buttons)
- 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.
Comments on this blog
This blog has 3 comments: