Advanced controls in userforms with VBA macros
Part nine 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
  8. Option Buttons (Radio Buttons)
  9. The Calendar Control (this blog)

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.

The Calendar Control

If your form needs to get a date from a user, use the built-in Calendar control:

The vanilla calendar control

The vanilla version of the Calendar control, with default formatting.


Creating a Calendar Control

You can add the Calendar control from the Toolbox:

The Calendar tool

Click on the Calendar tool, and click where you want this to go.


If you can't see the Calendar tool, you may need to install this additional ActiveX control.  To do this, right-click on the Toolbox and choose Additional controls.  It's amazing how many there are!

Coding Calendar Controls

You can pick up on the Value property of a Calendar control to get the date chosen by a user:

'date of order

ActiveCell.Offset(0, 6).Value = Me.calDate.Value

Additional Formatting for Calendars with Custom Properties

Because the Calendar control isn't a standard tool in the Toolbox, some properties are hidden away in the Custom category:

The calendar control Custom property

Click in the Custom property and then click on the build button.


You can then play about with all sorts of formatting options in the dialog box which appears:

The Custom properties dialog box

It's worth looking at all 3 tabs of this dialog box.

And with that, I've reached the end of this blog on advanced controls.

If you're wondering why I haven't included anything on the ToggleButton, ScrollBar or RefEdit controls, it's because I don't see the point in them.  I'm willing to be persuaded otherwise by comments added to this blog.

This blog has 0 threads Add post