Controls

Excel offers you to add controls to documents, the same elements that you can find in windows and dialog boxes, and they enable choosing values, checking options, adding labels, choosing from list objects… Adding control to a worksheet increases interactivity between user and reports. In this way, you can easily create different polls and save the results in a form suitable for further processing.

Controls are located in the Developer ribbon. If you have not previously turned on its view, right-click on File, click Customize Ribbon, and then add it in order to be visible. Clicking the Insert button opens a menu that offers two control groups: Form Controls and ActiveX Controls. The first are more suitable for adding to the Excel worksheet, and others when writing programs in the VBA language. Here we will deal with the first group of controls.

The first control we can select is Button. With it, we add the button to which, right at the start, we can join a macro. If you right-click the button, the context menu will open, and at its bottom you can find the Format Control option. By choosing this option, the dialog window opens in which we can set different control properties. In particular, for a Button control, we can adjust the type of font, color, size, and alignment of the text. We can also change the text itself. Some other controls have additional features, which will be discussed more in the text below.

Check Box and Option Button are used when we want to make some choices. We use the first one when we want to check or uncheck certain value. In the case of the other, we add more choices, so we choose one of them. In both of these controls, we can set the initial state, as well as assign the value of the choice to the cell in the Excel worksheet. Check Box returns the value TRUE or FALSE, depending on whether an option is selected. Option Button (Radio Button) returns the value of the index, which is the ordinal number of the selected option.

List is a control that allows you to add more options to the list and then choose one or several of them. In the Format Control window, we can set the Input range, as well as where the Cell Link–  index of the selected value will be kept (this only applies if we select a list that supports the choice of a value). Combo also presents a list, just collected. Clicking the arrow on the right will open a list of options from which we can choose the desired one.

Label is used to add captions, Spin Button to increase or decrease the value of a cell. Group Box can be used to group multiple controls. By combining controls with the usual Excel features, especially with the Data Validation option, you can create functional questionnaires.