This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 blog) 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.

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 blog)
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.

Posted by Andy Brown on 28 February 2012

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!

Parts of this blog
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 blog) Option Buttons (Radio Buttons) The Calendar Control

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Check Boxes

Adding a CheckBox

Formatting a CheckBox

Picking Up on a Checkbox's Value

Head office

London

Manchester