Form Controls / ActiveX controls

In Excel we have some controls that we can add to our spreadsheet. These controls are divided into two categories: the form controls and the ActiveX controls.
Form controls are used when we want to assign a macro to a control and ActiveX controls are used when we want to add Visual Basic code to a control.
In versions 2007 and 2010 we find these controls in the ribbon under the “Insert” button in the “Developer” tab.

In previous versions, we find them back in their own toolbar. In those cases, just click the “View” button in the menu bar, choose “Toolbars” in the drop-down menu and click the toolbar you want to see (as in below images).

I will not discuss all of these controls, only a few. But I’ll show you the difference between a Form Control and ActiveX control.

Let’s start with the most used control, the “button”.

Button

The first example is the form control “Button”. As we did earlier, we link a macro on a form control, so we get our macro. You can even do so after drawing the button, but why the delay.
As an example I created a macro to print the document.

Now we apply this macro to a button.
We select the form control “button”, and draw a button on the worksheet. Click and drag this into the worksheet.
When you release the mouse button, the “Assign Macro” window opens.
Select the macro and click the OK button.
This places the button in the worksheet:

To change the text in the button, select the text, and type the desired text.

To continue on the button, right click it and choose “Format Control”.

This opens the dialog box “Format Control” where we have 7 tabs.
Most options seem obvious to me, yet I can only tell you when you do not want the button to print, go to the “Properties” tab and deselect the text “Print Object”.

The second example is the ActiveX control “Button”.
We select the ActiveX control “button,” drag and click on the worksheet.
Release the mouse button, this will not open the “Macro window”, but will get button with the text “CommandBut”.

If you have an ActiveX control, you automatically draw in “Design Mode”.
This is also necessary to make changes to this control.
When “Design Mode” is selected, you can make any changes to the command button.
Right click above the button, and choose “View Code”.
This opens the “Visual Basic for Applications”. The VBA window is divided into three parts by default. In the middle part, the majority, is where we can enter the VBA code. On the left side we have the “Project” window and the “Properties” window. The “Project” window shows us each worksheet (C) and each workbook (B) which are open in Excel.
Double click another object, the object will open up another “code window” with all code – code that is added to that object. The extra object “ThisWorkbook” (D), is what you would use when such an event handler is added to your workbook, for example, to automatically save your workbook when you close it.
All these windows can be closed by clicking the cross icon (A) in the left corner of the window. You can then return by clicking on the “View” button in the menu bar and select “Code” for the code window, “Project Explorer” for the “Project” window and “Properties Window” for, you guessed it already, the “Properties” window.
In the VBA code window, you see the code “Private Sub CommandButton1_Click ()”. What this means is that a subroutine in a command is given to the “CommandButton1” when it is clicked. It is “Private” because this command belongs only to this particular button.

In this subroutine, you still have to enter a command, otherwise it will not do much. As an example I will add the same command as I added in the “macro button”. Namely printing the spreadsheet. The VBA code for this is “Printout”. I place the code between the beginning and the end of the subroutine (1). Remember that you put in all the VBA code in subroutines.

The preparation of a VBA button is much more extensive than what we can do with a macro button. All these settings can be changed in the “Properties” window. For example, I changed the name of the button, the background color, text (caption) in the button and the font (Font). If you are finished with the adjustments of the button, close the VBA window.

When you preview, you can clearly see the difference between the two buttons.

If you want to test the VBA button, click the “Design Mode” button again, so it is no longer selected.

More about Visual Basic in the following lessons.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats