Form Controls and ActiveX Controls - Part2 (explained in plain english)

Form Controls and ActiveX Controls - Explained »

Jul 26

Form Controls and ActiveX Controls - Part2

Hi there,

We have learnt what is Form Controls and What is ActiveX controls and where are these controls available in Excel in "Form Controls and ActiveX Controls - Explained". However, we will dig into this topic to learn more about it.

Now, let's see

How many controls are available in Form Controls?

There are 12 Form Controls. Here they are:

...

Button Control
Combo Box Control
Check Box Control
Spin Button Control
List Box Control
Option Button Control
Group Box Control
Label Control
Scroll Bar Control
Text Field Control
Combo List Control
Combo Dropdown Control

Note: Images of option 10, 11 and 12 are not posted here on the top because those are not covered in this post and those options are only available in the prior versions of Excel.

How many controls are available in ActiveX Controls?

The controls are same as above from 1 to 9 except Group Box and

9. Image Control

10. Toggle Button and almost 146 are hiding in more controls icon. We can even register custom controls.

How to insert the Controls to the worksheets?

It is very simple

Click on any of the control
Place the cursor and draw a triangle shape where required in the worksheet
Then, customize the shape, size, color etc.,

Brief on each Form Controls:

Button Control:

This is also called as PUSH button.

Once you insert the button on the worksheet, right click on the button and change the text to your understanding. Here in the image changed from "Button 3" to "exceltoxl" manually.

And, the button text can be linked to a cell by addressing through formula bar. Click on the button and goto formula bar and select the cell address like in the image it is $G$4.

We can change the color and font of the text on the button. If the text is not changing then goto formula bar and edit the cell address of the button and hit enter. This will definitely change the color and font of the text.

Assigning a Macro: To assign already existing macro to this button just right click on the button and select Assign Macro and choose the macro you want to assign and click OK.

Combo Box Control:

Combo Box allows the selection one or more than one item from the dropdown list. The dropdown list will be sourced from G4:G18.

Once you place the combo box to the worksheet

Right click on the button and
Goto Format control or CTRL + F1 keyboard shortcut
Then, Goto Control Tab (last one)
Click on blue red combination icon to select the range of the list
Click OK
Then, select the next similar icon to select the cell link.
Cell link will place the index number of the selected drop down.
Click OK (Magic..!)

We can also check mark the 3D - shading to show the Combo Box in 3D shade.

Check Box control:

Check Box control is the control which acts independently to release the output. This control will either give TRUE [when checked]or FALSE [when unchecked] as an output in the linked cell.

As usual, place the cursor after clicking on the icon and draw any shape on the worksheet. Once placed the control right click on it and choose 'Format Control' option. A pop-up will appear, last tab by name 'Control' find the 'Value' in the window. Choose either unchecked or checked then move on to 'Cell Link' and link to the cell here it is $D$4 for first check box and $D$7 for second check box. Click OK

When you check and uncheck the button the value either TRUE or FALSE will appear in the linked cell (D4 or D7).

Spin Button Control:

Spin Button control is a control which allows to increase or decrease by one value as defined in the 'Format Control' of the linked cell.

Right click and goto Format Control -->Control -->

Minimum value, Maximum value and Incremental change should always be an integer and should be entered by the user.

Link the cell link; here it is F3 and click OK

Magic works now...! The incremental and decremental values will appear in the linked cell F3.

List Box Control:

The List Box just works like a Combo Box.

Option Button Control:

Option Button control is a control which displays the selection number in the range of Option Buttons.

For eg: we have 5 option buttons when we check the second button the linked cell gives the index value as 2 similarly the other options.

Here what we need to notice is that even if the option buttons are 5 we need to give cell link to one Option Button only, the rest will automatically works on the basis of the first one.

And, the previous checked button will be automatically unchecks when we check the unchecked button.

Group Box Control:

Group Box control is actually a control which does not have interactivity like we see till now. This is something like a group fitted with different options related to one single entity. Like in the example workbook I have shown the new English movies released in this week. In this entity I have choose few new released movies to choose which one to watch in this weekend. I have used check box because if anyone wants to see more than one movie they are welcomed to watch. :)

Label Control:

Label Control is a control which generates label/text of the linked cell or the text/label given to the control.

Here in the example worksheet, click on 'Now I am learning Label Control' and right click on it will get 'Exit Edit Text' this means that I have entered manually to display the text on the Label Control.

However, if we check the second one 'Now I have Learnt Label Control' and right click we don't see the 'Exit Edit Text' because this Label is from the linked cell (I10).

How to link the text to the Label? Just select the cell where Label Control is placed and goto formula bar and type = and link to the target cell here it is I10.

That's it Magic works now...!

Scroll Bar Control:

Scroll Bar control is a control which indicates the shape of a Slicer; this option is introduced in Excel 2010 and above. This works like an up/down arrow or left/right arrow. I mean to say Scroll Bar can be placed either horizontally or vertically to change the values by single integer. This works like a Spin Button. Like Spin button here also we need to assign integer values to incremental value, maximum value, minimum value as well as page change up/down.

Check the example in the worksheet which shows the value in the D40 is due to Vertical Scroll Bar and the value in H2 is due to Horizontal Scroll Bar.

Text Field Control:

Combo List Control:

Combo Dropdown Control:

These three controls are not active in Excel 2007 and above. To use these controls we need to use Excel version 5.0 only. The earlier version is not available with me to explain this so, I am ignoring these form controls.

Brief on ActiveX controls:

ActiveX controls will work similar to Form Controls however they are newer than Form controls so ActiveX controls have more options than Form Controls.

How to Move or Resize Form Controls?

We can right click the control and press Escape Key in the keyboard [ESC] then the control get selected or the control is in edit mode then we can move from one place to another place and similarly hold the corners or mid of the controls to expand or to compress the controls.

Hold ALT key while moving or resizing the controls to fit in to the cell.

Download the working file from here

Did you use Form & ActiveX Controls???

Have you used these controls. Do you have different experiences while using these controls then what are you waiting for use this space to share your words on this topic in the comment section below. You can leave an email to nourgent@yahoo.com if you feel to share it here. Your ideas and suggestions are always welcome in this space. Learn and let others learn through our experiences.

If you find this informative, please do share and like this post on your social network walls, available towards your left hand side and on the bottom of the page.

Some Important Links: