Note: The Design Mode icon in the Developer ➪ Controls group appears highlighted when Excel is in Design mode. Click this button to toggle Design mode between on and off. When Excel is in Design mode, you can’t work with the controls. To test whether your controls work properly, you have to exit the Design mode.
Just click on the highlighted Design Mode icon to exit Design mode. Many times you will need to change the state of the Design Mode( from on to off or vice versa) when you are working with ActiveX controls in your worksheet. Changing/ Adjusting properties Whatever control we add to our worksheet, has various properties that determine how it looks and works. If Excel is in Design mode, only then you can think of changing/adjusting properties of controls. When we create a control in a worksheet, Excel enters in Design mode automatically. To change the properties for a control, follow the steps below:.
Excel General If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. Excel difference between Form Controls vs ActiveX Controls — This Excel tutorial explains difference between Form Controls vs ActiveX Controls for Excel worksheet. Both these buttons are also known as a push button, which can be set up to automate the printing of a worksheet, filtering data.
At first, you have to make sure that Excel is in Design mode. Click the control whose properties you want to adjust. The control will be selected. Now click the Properties icon in the Controls section of the Developer tab. The Properties window appears as shown in the figure below. Use the Properties window to change the properties of a control.
Here shown an CommandButton control properties. Select the property that needs change/adjust and make the change. You will find that some properties show the value for them in a drop-down list from which you can select a value for the property. Some properties (such as Font), have a button. Click the button, you will get a dialog box to change the property. Other properties require you to type in the property values. The changes in property values show an immediate effect.
Tip: To find out about a particular property, select the property in the Properties window and press F1. The Properties window has two tabs.
The Alphabetic tab shows the properties in alphabetical order. The Categorized tab displays the properties by category.
Both tabs show the same properties; only the order is different. Read More: Common properties Each control has its own unique set of properties. However, many controls have common properties. In this section, I have covered some properties that are common to all or many controls. Property Description BackColor The background color of the control.
BackStyle The style of the background. It may be either transparent or opaque.
AutoSize If AutoSize is True, the control resizes itself automatically, based on the text in its caption. Caption The text that appears on the control. LinkedCell A worksheet cell that contains the current value of a control. ListFillRange A worksheet range. It contains items displayed in a ComboBox or ListBox control. Value The control’s value.
Left and Top Values that determine the control’s position. Width and Height Values that determine the control’s width and height. Visible If False, the control is hidden. Name The name of the control. Default names are based on the control type. For example, CommandButton controls are named like CommandButton1, CommandButton2 and so on.
But you can change the name to any valid name. However, each control’s name must be unique for a specific worksheet. Picture Enables you to specify a graphic image to display. Linking controls to cells Most of the time, ActiveX controls can be used in a worksheet without using any macros.
Many controls have a LinkedCell property. LinkedCell property holds the reference of a worksheet cell and this cell is linked to the control. For example, say I have inserted two OptionButton controls on a worksheet like the following figure.
VB Editor opens with a code. You can create Macro for any event listed under the control. Tip There is an easy way to access the code module for a control. Just double-click the control while Excel is in Design mode. The control’s name(in our case it is CommandButton1) appears in the upper-left portion of the code window, and the event(Click) appears in the upper-right area. Read More: I have used a step by step process to insert a CommandButton control in a worksheet and then created a simple macro that displays a message when the button is clicked:.
Choose Developer ➪ Controls ➪ Insert. Click the CommandButton tool in the ActiveX Controls section. Click anywhere in the worksheet to create the button. Excel automatically enters into Design mode. Right-click on the button and select Properties from the shortcut menu. In the properties window, change caption from “CommandButton1” to “OK”.
Double-click the button. The VB Editor window is activated, and an empty Sub procedure is created. Enter this VBA statement before the End Sub statement: MsgBox “Hi Friend!, it’s ” & Time.