Forms Controls and ActiveX Controls in Excel
by Jon Peltier
Monday, May 12th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
There are two types of controls for use in Excel worksheets: Forms controls and ActiveX Controls. The Forms controls aren’t very complicated, and they have been part of Excel for longer (I’m thinking Excel 4, and they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet. Some advanced users have attributed buggy behavior to the ActiveX controls from the Controls Toolbox. I’ve recently had problems with a buggy slider from the Forms toolbar, but I suspect the parent workbook was corrupt.
There are outward similarities, mostly in appearance, between Excel’s Forms controls and ActiveX controls. The ActiveX controls are actually more similar to the ActiveX controls available within VBA for use on UserForms. In Classic Excel and VBA, Forms and ActiveX controls are available on toolbars, while in Excel 2007, they’ve been moved to the Developer tab of the Ribbon.
Forms Controls
ActiveX Controls
VBA Controls
Forms and ActiveX Controls in Excel 2003
Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel. (Neither does VBA at all in fact, if you’re using MacOffice 2008.) Other Office applications also have appropriate selections of controls, though Excel has the widest selection.
Excel responds to a Forms control after the user finishes interacting with it (i.e., unclicks). Excel responds continuously to an ActiveX control. If a Forms slider control is linked to a cell’s value, the user has to slide the bar on the control, then release it before the cell updates. If an ActiveX ScrollBar is linked to the cell’s value, the cell updates continuously as the user slides the bar along the control.
In general I use the Forms controls, unless I need the continuous response of an ActiveX scrolllbar.
Formatting Controls
Forms controls can be formatted as if they were regular shapes (Excel 95 to Excel 2003 shapes; the new shapes in Excel 2007 are different animals altogether), although the options are not as extensive as for shapes, and vary for different control types. The button dialog (below) is much like that for shapes with various Font, Size, Alignment, and similar tabs, although the button is stuck with its gray color and rectangular shape. A regular shape can be assigned a macro in much the same way, with all the shape’s formatting options.
Also depending on the control, there is a tab for properties specific to the control. For a Drop Down or List Box control, there is a means to select an input range, containing the list of items appearing in the control, and another for a cell link, which is where the selected item in the control is stored.
ActiveX controls have a scaled back Format Control dialog, but a rich formatting environment offered by the Properties window. In addition, some controls have properties which indicate the addresses for List Fill Range and Linked Cell.
Using Controls with VBA Procedures
Forms controls can have macros assigned to them (see Assign a Macro to a Button or Shape). Clicking on the control runs the macro. The Assign Macro dialog appears when a control is first created, or when the corresponding item is selected from the right click menu. Any macros in the active workbook or in any open workbooks appear in this list. The default macro is named like an event procedure (“Click”), but that seems appropriate to the ActiveX Controls.
The ActiveX controls have event procedures that run when they are clicked (see Assign a Macro to an ActiveX Control). These procedures are either inserted in the code modules of the objects (i.e., worksheets) within which they are embedded, or written in class modules. Right click on an ActiveX control and select View Code: The shell of the default procedure is inserted into the code module of the object the control is embedded within (i.e., the worksheet). Enter the code here that should be run when the control is clicked.
Note the two dropdowns at the top of the code module. The left dropdown lists all objects associated with the object, including the object itself (Worksheet) and its embedded controls.
When an object is selected in the left dropdown, the right dropdown shows the events available to that control. This provides the ActiveX controls with much greater functionality than the Forms controls.
Related Posts:
- How To: Assign a Macro to an ActiveX Control
- How To: Assign a Macro to a Button or Shape
- Sample Parallel Coordinate Chart
- How To: Assign a Macro to a Toolbar or Menu
- Make Your Recorded Macro Independent of Which Sheet is Active
- Dynamic Chart using Pivot Table and VBA
- Programming Excel 2007 and Excel 2010 AutoShapes with VBA (Guest Post)
- How To: Use Someone Else’s Macro
- Book Review: Pro Excel 2007 VBA
- Using RefEdit Controls in Excel Dialogs
Posted: Monday, May 12th, 2008 under VBA.
Comments: 15
Comments
Comment from Edgar Hungers
Time: Wednesday, May 27, 2009, 4:38 pm
Hi, Great pages and information from. You.
This time I have a question, maybe you could help me solve it.
I inserted a ListBox on a Chart. I used the listfillrange property to insert the values i needed. But after I resize the listbox or after i apply this line of code: listBox1.formatcontrol.Removeallitem to re-populate it. The values? or the font? inside the ListBox kind of disappeared or get chunked. Please try it and if you have any answer. Please Tell me. This is giving me a real headache
Thanks
Comment from Jon Peltier
Time: Wednesday, May 27, 2009, 5:37 pm
I’ve never noticed this, I expected it would be a version-specific problem, but I saw the problem in Excel 2003.
I don’t know how to avoid the issue.
Comment from Edgar Hungers
Time: Wednesday, May 27, 2009, 7:03 pm
Hi again fellow,
I’m sorry for not to mention that i was in Excel 2003. and yes it’s been really upset to manage that, more of that is that I cannot use the additem method. i mean doesn’t work rigth. I’m going and try again. and stiil wanting to fix it. cause i want to change my chart’s source according to selections made on that listbox. Please keep trying. I’m really counting more on your skills and expertise than in my willing. but i’ll keep trying
Comment from Jon Peltier
Time: Wednesday, May 27, 2009, 8:08 pm
Well, if one approach fails, another might do better.
If you have to use a chart sheet, pop up a userform with a listbox. It requires maybe a bit more programming, but it should work fine.
If you can use an chart embedded in a worksheet, embed the listbox in the sheet next to the chart. This worked fine in my quick testing.
Comment from Edgar Hungers
Time: Wednesday, May 27, 2009, 9:51 pm
Jon;
Yes, in a worksheet it works fine, I already had tested that, as long as I use the AxtiveX ListBox from the Toolbar and not from the Forms Bar, thus giving me more functionality and capacity of changing its properties. But I need to use the Chart sheet for a profesional approach of the statistics I’m presenting. However, the ListBox from The forms Menu, only one avilable on the Chart Sheet, has this kind of Object Class developed-bug. It seems as if it was not fully developed. I mean the Listbox Class for this instance. Besides that When I move it, it leaves some Object copy from the position where I move it. It’s not right this Object Class definitly. But the ComboBox, It works fully Ok. And I already have one on My chart, so I wanted to use the ListBox to show a sub-Clasification for the data the user to watch on that Chart. And the ListBox seemed to fit perfect for that, but I see I’ll have to use another ComboBox, as far as now.
Jon, Dont let this go by, if you have the chance to consult this with someone else, please try to see if there is a debugger for this and let me Know.
Thanks for your support. Regards
Comment from Edgar Hungers
Time: Wednesday, May 27, 2009, 10:01 pm
Also, just a couple of questions:
Why is it that the Toolbar and its ActiveX controls aren’t available on a ChartSheet ?There would be some Kind of Add-in that could make them Available?
This conversation takes me to consider that we both do not Know the reasons, if not. You’d have given me the hints. Anyway, maybe you could reach a more comprehensive understanding of this than me. And maybe give an answer to that.
Comment from Jon Peltier
Time: Thursday, May 28, 2009, 6:11 am
If you are going to move to 2007 anytime soon, you may as well get used to a worksheet-based display. Excel 2007 has removed the ability to size a chart based on the window size. You can hide gridlines and make the sheet look like a professional display.
Like the chart window sizing thing in 2007 or the flaky listbox beehavior, it’s better not to ask why Microsoft does what it does or when Microsoft will do what you want, nor to expect that people have done the impossible in an add-in. It’s better instead to accept the way things are, work within the constraints, and hope they don’t mess it up in a future version.
Comment from Edgar Hungers
Time: Thursday, May 28, 2009, 8:06 am
Well, that’s something to understand, I’ll keep within the constraints and use What I know it’s working fine. Thanks for those reminds and for the suggestions too.
Hope to bother you sometime again
Comment from james
Time: Monday, February 1, 2010, 12:44 pm
can you make the scroll bar in excel 2007 change shading as the value increases. For instance, as you slide the slider towards the right, can the area on its left change color to match the value, similar to conditional formatting
Comment from Jon Peltier
Time: Monday, February 1, 2010, 1:16 pm
James -
There is no built in way to dynamically format a scroll bar, but if you use a Controls Toolbox scrollbar, you can fake it:
' ScrollBar1 has min=0 and max=255
Private Sub ScrollBar1_Change()
Dim iVal As Long
iVal = Me.ScrollBar1.Value
Me.ScrollBar1.BackColor = RGB(iVal, iVal, iVal)
End SubIt isn’t perfect: at the lower part of the range the shading is grainy. But you could adjust your algorithm to stay in the range that renders nicely.
Comment from Erich
Time: Wednesday, March 31, 2010, 3:15 pm
Hi Jon-
Love the website, use your tips weekly it seems! Quick question – I am using 2003, and have a chart w/ a form control scrollbar to move through time on the x-axis. When a user clicks on the arrow (left or right) on the scrollbar and makes no other movements with the mouse, the scrollbar will continue to scroll. Do you know anyway to have 1 click=1 movement? I would think it would be this way already…
Comment from Jon Peltier
Time: Wednesday, March 31, 2010, 5:28 pm
Erich -
What the scrollbar really does is move one step, then when it detects that the mouse button hasn’t been released, it helpfully decides you still want to keep moving.
In 2007, the forms controls only move one step until you explicitly release and repress the mouse button. After trying both just now, I think I prefer the 2003 behavior.
Comment from Erich
Time: Thursday, April 22, 2010, 1:26 pm
RE: Scrollbar Behavior
Completely agree with your post that 2003 behavior is better. After re-reading my post, I realize that I did not mention that the user has released the mouse button – and the scroll bar keeps scrolling.
I’ve scoured the web and found the below. It looks like the same issue I am having only not in excel, so not helpful in this case.
Comment from Pete Kies
Time: Wednesday, August 11, 2010, 12:51 pm
Jon,
I have been developing an application with a dynamic chart that has several form controls to display equipment operating data versus time, up to 10 series at once. On occasion, I find that the macro assignments on all of the controls get removed, and I need to relink them. Are you aware of any situations that could cause this? At first I thought it might be in my workbook Auto_Open routine or another sub that replicates worksheets for data entry – but it does not happen every time I run these, so I suspect the problem may be somewhere else. I have probably opened other workbooks with macros enabled and disabled while working on this. The file is ~4 MB and there are ~2 dozen controls, so I don’t think it is a memory issue.
Any suggestions on how to avoid this would be appreciated.
Comment from Jon Peltier
Time: Thursday, August 12, 2010, 1:46 pm
Pete -
What version of Excel? Sometimes files that get opened in both 2003 and 2007 have problems. I have had very few problems with Forms menu controls, but extensive issues with ActiveX controls, in this situation. But a client recently described problems with Forms controls. Part of his problem was related to buttons that had assigned macros from other workbooks.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.