Other ActiveX Controls in Excel Worksheets

In an earlier post, we looked at connecting VBA code to command buttons on the Worksheet, the Quick Access Toolbar and the Ribbon.  What about the other ActiveX Controls: The Combo Box, List Box, or Check Box?  How about Option Buttons, the Scroll Bar, Spin button, Toggle button, etc.?  That’s coming up in this post.

If you aren’t aware of the distinction between Form Controls and ActiveX Controls, first look at the previous post.  It also deals with Form Controls.  This one deals with ActiveX Controls.

1.        Where are the ActiveX Controls?

These are on the Developer Ribbon, in the Controls group.  Pull down the Insert dropdown menu.  The ActiveX Controls list is below the Form Controls list.  All these controls have properties that you can change – see below.  For more information about ActiveX controls, see Visual Basic Help.

• Command button            A button to run code when it you click it.  The recommended prefix to a Command button name is cmd.  Example: You might name your OK and Cancel buttons cmdOK and cmdCancel.
• Combo box                    A text box with a drop-down list box. You can either type or select a choice in the box.  The recommended prefix to its name is cbo.
• Check box                      A tick box to turn an option on or off.  The recommended name prefix is chk.
• List box                          A box that contains a list of items.  The name prefix is lst.
• Text box                         A box in which you can type text.  The name prefix is txt.
• Scroll bar                        A control that scrolls through a range of values.  The prefix is scl.
• Spin button                     A button with up and down arrows to change the value in a cell.  Prefix: spn.
• Option button                  A button used to select one of a group of options.   Prefix: opt.
• Label                              Static Text to provide information.  Prefix: lbl.
• Image                             A control that contains a picture.  Prefix: img.
•                  A button that stays “pressed in” when clicked.  It then releases when clicked again.   Prefix: tgl.

The last button, More Controls, brings up a list of all the ActiveX controls loaded on your PC.  They may or may not work with Excel and Visual Basic.  If you use any of them, make sure that the ones you use are loaded on every PC that will use that Excel file.

As with the Command Button we used before, you can change the Properties of the control.  Be sure to give it a sensible name first! Then add code using the View Code button.

2.        ActiveX Control Properties

Right-click on your new ActiveX control and, from the pop-up menu, choose Properties.  This pops up a floating window named “Properties”:

Property names are in the lefthand column in the window.  You can’t change them, but you can view them in alphabetical order or in categories, using the tabs at the top.  The property values are in the righthand column. You can change them.

The first property to change is “(Name)”.  Rename it to a meaningful name that starts with the correct prefix as defined above.  For example, use cboBarSizes for a Combo Box of reinforcing bar. diameters.

Other important properties for different ActiveX Controls:

3.        Common Properties for ActiveX Controls

The various types of controls have different properties.  Still, here are the useful ones that several have in common:

• Left, Top                         Position of the control: Left and Top coordinates in points.
• Height, Width                  Size of the control: Height and Width in points.  The size and position are set when you draw the control.  Then you can change them manually or in code for more precise positioning.
• Font                               Font settings (name, style, size etc).
• ForeCoIor                       Foreground colour, where this can be set.
• LinkedCeII:                     The cell address to receive the result.
• MaxLength                      The maximum length of text entries (0 = no restriction).
• MousePointer                 Sets the type of pointer shown when the user hovers the mouse over the control.  Values:  0-15 (see list here or in Properties). 99 = use Mouselcon.
• Mouselcon                      A custom picture to use as the mouse pointer, if the MousePointer property = 99.
• Placement                      the way the control attaches to the cells below it: 1 = moved and sized with the cells, 2 = moved with the cells, 3 = free floating.
• PrintObject                     Should Excel print this control on printouts? (False is usually best)
• TextAIign                        Alignment of text:  Left (default), Centre, or Right.
• Value                              The chosen value for controls that return text or another value.
• Visible                            True if the control is visible.  You might use code to set it to False in cases where you want it hidden.
• Enabled                          True if the control is active and available.  You might use code to set it to False in cases where you want it visible but not in use at the time, such as when prior input is absent..

Then, the important properties for some specific controls:

4.        Properties of the ActiveX Combo Box (many apply to the List Box too)

While this control has many properties, here are the more important ones to know about:

• ColumnCount:                 The number of columns (1 by default).
• ColumnWidths:               The widths, in points, for multiple columns. Separate them with commas or semicolons (blank, for 1 column = same width as the control.  Size the rightmost column larger to allow for a vertical scroll bar if the rows in the list exceed ListRows.
• ListRows                        Number of the rows in the list.  The default (8) is too small for many applications.
• ListWidth                        Width of the whole list, in points.  If this is less than the sum of the ColumnWidths, the list will have a horizontal scroll bar.  If more, then the rightmost column will take up the surplus.
• LinkedCeII:                     The optional cell address to receive the result.
• ListFiIIRange:                 The range from which to get the data (type this in).  The number of columns should match the ColumnCount property.
• BoundCoIumn                 The column number from which the data goes to the LinkedCeII.
• ColumnHeads                 True = use the top row of ListFiIIRange as headings at the top of the dropdown list.
  False = No column headings.
• MatchEntry                     The rule for matching what you type to entries in the list.
  0 (fmMatchEntryFirstLetter) = Basic matching. The control searches for the next entry that starts with the character typed. Repeatedly typing the same letter cycles through all entries that start with that letter.
  1 (fmMatchEntryComplete) (default) = Extended matching. As you type each character, the control matches the first list item starting with all those characters.
  2 (fmMatchEntryNone) No matching (pretty useless).
• MatchRequired                True = The text you type must match an existing list item.
  False (default) = You can enter text that does not match any list item.  In this case, you would need code to add that item to the list.
• ShowDropButtonWhen    When to show the dropdown button: 0 = never. 1 = when control has the focus. 2 = always (default and the only sensible choice).
• SpecialEffect                  Control appears: 0 = Flat, 1 = Raised, 2 = Sunken, 3 = Etched, 6 = Bump.
• TextCoIumn                    The number of the column in a ComboBox or ListBox to store in the Text property.  That’s the Text box part of the Combo Box. 0 displays the ListIndex values (the number of the row in the list, zero base). -1 (default) displays the first column that has a Column Width greater than 0.

5.        Properties of the ActiveX Command Button, Toggle button, Check Box, and Option Button

While these controls have many properties, here are the more important ones to know about:

• Caption:                          The text displayed on the button.
• Font:                              The typeface and size for that text.
• Accelerator:                    A letter to press along with the Alt key to press the button. That shortcut letter will appear underlined in the caption.
• TakeFocusOnClick:         (Command Buttons only) True = The button takes the focus when clicked (default). You might set it to False so that clicking the button does not take the focus away from the selected cell.
• Alignment:                      (Check Box and Option Button) Placement of text: On the Right of the Box/Button, or on the left.
• GroupName:                   (Check Box and Option Button, but only useful for the latter). With Check Boxes, you can select any number of them.  But with Option Buttons, you can select only one of a group.  To create independent sets of Option Buttons, give each group a different Group Name.
• TripleState:                     (Check Box and Option Button, but only useful for the former). If True, the Check Box can have three Values: The usual True (checked) and False (blank), plus Null (shaded).
• WordWrap                      True = a Caption too long for the control’s width will wrap to a new line.
• Picture:                           Optional picture or icon to display instead of the Caption.

6.        Properties Specific to Other ActiveX Controls

Text box:

• AutoTab:                        True = an automatic tab occurs when text length reaches  MaxLength characters.
• Font:                              The typeface and size for that text.
• MultiLine:                        If True, the TextBox allows line breaks (Shift+Enter and Ctrl+Enter).
• PasswordChar:               Placeholder used in place of any character (for non-display of passwords)
• WordWrap:                     Allow word wrapping if MultiLine is True.
• ScrollBars:                      None, Horizontal (single line), Vertical (multi-line with word wrap) or Both (multi-line without word wrap)

Scroll bar and Spin button:

• Delay:                            The millisecond delay between events for the SpinUp, SpinDown, and Change events.
• Max and Min:                  The limits of the values allowed for the control.
• ProportionalThumb:         True = the scroll box (“thumb”) is proportional in size to the scrolling region (default).
• SmallChange:                 The value of the change when the user clicks a scroll arrow
• LargeChange:                 (Scroll bar only) The value of the change when the user clicks on the scroll bar.  That is, between the scroll box and a scroll arrow. Typically, you would set this to 5 to 10 x SmallChange.

Label:

• Accelerator:                    A letter to press along with the Alt key to click the label. That shortcut letter will appear underlined in the caption.

Image:

• Picture:                           The picture to display.
• PictureAlignment:            where the picture aligns with the control. 0 = top left, 1 = top right, 2 = centre, 3 = bottom left, 4 = bottom right.
• PictureSizeMode:            What to do if the image control and the picture it contains are not the same size.  0 (default) = Clip/Crop keeps the picture at its original size. 1 = Stretch the picture to fill the control: This may distort the aspect ratio. 3 = Zoom the picture to fit the control’s height or width, maintaining the aspect ratio (best choice).
• PictureTiling                    True = Fill any whitespace with parts of the picture. This is for PictureSizeMode = 0 or 3.

7.        Attaching VB Code to ActiveX Controls

Right-click on your ActiveX control and, from the pop-up menu, choose View Code.

For some examples of code that runs from ActiveX Controls, get the file Macros.xlsm free of charge from us. Look at the code behind the sheet “ActiveX Controls”.  It demonstrates a variety of events with the controls shown:

• Sub cboSoils_Click                                 Combo Box Click
• Sub txtName_Change                            Text Box Change
• Sub imgAnim8_MouseMove                  Image MouseMove

Other useful events are GotFocus, LostFocus, DblClick, and KeyPress.

The code will always be in the code of the Sheet on which the control is, not in a Module.  From there, you can of course call code that is in a module.

That concludes our look at the properties of ActiveX Controls.