. We use ActiveX controls for flexible design requirement.
. ActiveX controls can be used on worksheets with or without use of VBA code.
. We can also assign a Macro to run directly from an ActiveX control.
. We can customize appearance, behaviour, fonts and other characteristics in properties of ActiveX controls.
Inserting Controls:
. We can add ActiveX Controls in Sheets which can function as per our requirement.
> Developer bar
> Select Design Mode
> Insert
> Choose controls
> Select Design Mode
> Insert
> Choose controls
Example:
1. Inserting Label:
> Choose Label control from Insert Tool bar.
> Drag the label at desired location.
> Change the properties by right click.
> In this example, code is written for displaying Grid lines.
> Choose Label control from Insert Tool bar.
> Drag the label at desired location.
> Change the properties by right click.
> Change the Backcolor, Name.
> We can also modify other properties as per requirement.
2. Inserting Text Box:
> Select the Text Box control from Insert toolbar.
> Change the properties.
> Here, the value written in TextBox appears in Cell "G4".
Result:
3. Inserting Spin Button:
> Select the Spin Button control from Insert toolbar.
> Change the properties.
> Add LinkCell value which changes as we click spin.
> Scrolling properties indicates range of incrementing values.
> We can see also add code to Spin Button.
> For Example, copying value in LinkedCell to another cell.
Result:
4. Inserting Option Button:
> Select Option Button control from Insert toolbar.
> Change the properties.
> Write code so that value selected is copied to a particular cell.
> Below code is for Male Option Button, if it is selected, it is copied to cell "I4".
> Similarly, another option button for Female is created and code is added to it.
Result:
5. Inserting Check Box:
> Select Check Box control from Insert toolbar.
> Change the properties.
> Write the code to copy the value to a particular cell.
Result:
6. Inserting Scroll Bar:
> Select Scroll Bar control from Insert toolbar.
> Change the properties.
> Set the scrolling properties as required.
> Add LinkCell to change value on scrolling.
> Write code to copy value from LinkCell to particular cell "K4".
Result:
7. Inserting ComboBox:
> Select the ComboBox control from Insert toolbar.
> Change the properties.
> Here, ListFillRange indicates the range of cells to be included in the Combo Box.
> LinkedCell contains the cell which displays the selected value from ComboBox.
Result:
8. Inserting a List Box:
> Select List Box control from the Insert toolbar.
> Change the properties.
> Here, ListFillRange indicates the range of cells to be included in the List Box.
> LinkedCell contains the cell which displays the selected value from List Box.
Result:
9. Inserting ImageControl:
> Select Image control from Insert Toolbar.
> Change the properties.
> Here, Picture indicates the image source, add image.
> Change the alignment, size of picture as you need.
10. Inserting Command Button:
> Select Command Button control from Insert Toolbar.
> Change the properties.
> Write the code to perform action when Button is clicked.
> In this example, color of headings are changed to Yellow when button is clicked.
Result:
11. Inserting Toggle Button:
> Select Toggle Button control from Insert toolbar.
> Change the properties.
Result: