Forms Controls and ActiveX Controls in Excel
by Jon Peltier
Monday, May 12th, 2008
Peltier Technical Services, Inc., Copyright © 2012.
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 2007
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
- Using Worksheet Controls to Highlight Selected Data in an Excel Chart
- Sample Parallel Coordinate Chart
- How To: Assign a Macro to a Toolbar or Menu
- Using RefEdit Controls in Excel Dialogs
Posted: Monday, May 12th, 2008 under VBA.
Comments: 34
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.
Comment from Mike
Time: Tuesday, April 12, 2011, 10:50 am
Jon, great explanation, however there’s a slight boo-boo with your graphic labelled “Forms and ActiveX Controls in Excel 2003″ the Excel version you have to illustrate the controls is actually 2007 not 2003. :P.
One question though: are form controls more “secure” from being exploited than those of ActiveX?
Thanks very much for the resources you provide.
Mike.
Comment from Jon Peltier
Time: Thursday, April 14, 2011, 10:16 am
Mike -
Thanks for pointing out that typo, I’ve corrected it.
I don’t know of any security exploits attributed to Excel ActiveX controls (I’m not saying they don’t exist). There are certainly some strange behaviors caused by these controls, though. Forms controls typically have fewer of these issues, and because they are not ActiveX (and lack the rich ActiveX features), I suppose you could consider them more secure.
Comment from Addy
Time: Tuesday, July 12, 2011, 4:44 pm
Hi Jon,
I am looking to take a set of Data with multiple columns (a Range) — use the ComboBox in order to filter the data based on one of the columns. Then filter then again through another drop down menu afterwards. Any suggestions on how I go about doing this? Or can you reference me to other websites which might have more information on this?
My VBA programming skills are limited– have been learning slowly through books.
Thanks.
Comment from Steve
Time: Monday, October 3, 2011, 11:06 am
Using ActiveX buttons in Excel 2003…
I have a case where the ActiveX buttons become totally non-functional (as opposed to disabled). They behave as if they are not even there; clicking over the button will hilite the cell underneath. Until….Enter Design Mode; right click to Edit the button; do nothing; exit edit mode. Voila! the edited button works again…for a while; other buttons don’t work until they are edited, too. Not sure exactly when, possibly after closing and re-opening Excel, the buttons go non-functional again.
I’ve converted to Forms controls, but I’d prefer to have some of the additional functionality (like disable) of ActiveX buttons.
Have any ideas on how to fix this permanently?
Comment from Jon Peltier
Time: Monday, October 3, 2011, 11:46 am
Steve -
If the controls were originally created in an earlier version of Excel, they may not work in Excel 2007 or 2010. Your best bet is to rebuild them in the newer version of Excel.
Comment from Steve
Time: Monday, October 3, 2011, 11:55 am
Unfortunately, I have to use Excel 2003 until next year some time. The controls were created in Excel 2003.
Comment from Jon Peltier
Time: Tuesday, October 4, 2011, 9:05 am
Steve -
I’d say “fortunately”, because in Excel 2003, ActiveX controls were only somewhat problematic. I’m surprised you’re having this kind of problem in 2003.
Comment from Steve
Time: Monday, October 10, 2011, 3:13 pm
Here’s more on the non-functional button. If two windows are open, the button control is functional only in the workbookName.xls:1 window; it’s non-functional in the “:2″ window. Now, if both windows show the same sheet and the button in :1 is clicked, the same button in :2 becomes hatched indicating it is non-functional. But, if the button is in the :2 window and :1 shows another sheet, the button is still non-functional and not hatched.
A quick check just showed that a non-functional button in the :2 window could be made functional by the edit process I originally described. So I suspect that I may have had two windows open and not realized it. Oh well….
So, do I infer from your last message that ActiveX buttons in Excel 2007 and beyond have greater problems?
Comment from Mark
Time: Wednesday, November 2, 2011, 11:09 am
I am trying to insert a drop-down calandar in my excel spreadsheet but I do not have the “Calander Control” as one of my selections in my Active X. Any ideas?
Comment from Jon Peltier
Time: Wednesday, November 2, 2011, 1:36 pm
Mark -
There’s no Calendar control native to Office. There is a VB one that has been bundled with Access, and can be accessed by Excel, but only (I think) in a userform. It’s tricky to use, and isn’t reliable as it may not be present on someone else’s computer.
Comment from ashley
Time: Sunday, November 13, 2011, 10:59 pm
Hi, is there anyway to use activeX form functions in excel 2003?
I’ve made a workbook using Excel 2007 and have added some check boxes.. when i sent the workbook (changed so it is compatible with 2003) to a friend she cant seem to ‘check’ the check boxes..
Comment from Jon Peltier
Time: Tuesday, November 15, 2011, 7:26 am
Ashley -
There’s a lot of funny business when ActiveX controls are passed across the 2003/2007 divide. The controls move, or resize, or refuse to work. In one project, I saw all of the ActiveX controls converted into pictures of themselves.
It has always been more reliable to use Forms menu controls rather than ActiveX controls.
Comment from Laura
Time: Wednesday, November 23, 2011, 1:00 am
So I made 4 buttons sheet1 and then double clicked the button so in VBA it showed
Private Sub Command Button1()
End Sub
and I am trying to get the button to link to and active a different worksheet in the same workbook. However, when I click the button, nothing happens except it opens the visual basic editor opens up to the private commandbutton1 and the coding Inserted to active the other worksheets.
Any suggestions?
Comment from Jon Peltier
Time: Wednesday, November 23, 2011, 8:48 am
Laura -
If you’ve inserted an ActiveX Controls Toolbox button, the auto-generated code should say:
Private Sub CommandButton1_Click()
End SubIn between you need to insert a line like
Sheets("My Sheet").ActivateThen you have to click to unhighlight the Design Mode button in the Developer tab. ActiveX controls do not work in Design Mode.
Comment from Lee Deverick
Time: Wednesday, December 14, 2011, 11:51 pm
Hi Jon
I have made an active X combo box for a dropdown list of suppliers. I have managed to format the list to font – Verdana, size 11.
However whenever I leave the combobox and move on to the rest of my spreadsheet, the combobox reverts to a different font that overlaps itself.
I have attached two screendumps in the following link
https://picasaweb.google.com/100377083605879170608/Excel?authkey=Gv1sRgCN2vnZqfuf-ozAE#
Can you please let me know if there is anyway I can rectify this.
Many thanks
Lee
Comment from Jon Peltier
Time: Thursday, December 15, 2011, 8:01 am
Lee -
You didn’t mention the Excel version, but I assume it’s 2007/2010.
As I mention in this article, ActiveX controls have more flexibility but also are unreliable. The problem is worse when the same workbook is used by both early (2000/02/03) and recent (2007/10) versions of Excel.
The workaround is to use the less flexible forms menu controls.
Comment from Jaime
Time: Friday, December 23, 2011, 11:16 am
Is there a way to use one button to auto changing data in the same row to sequential rows? So for example, I have a row of data that changes as the the key changes based on user input. When a user types in a location ID #, it auto populates a hidden row within the sheet ranging from R5:AC5 (using a series of vlookup statements that call out to another sheet within the workbook) and when the input field is empty the row is no longer populated (well, it is but it’s #N/A all the way across.) The user is supposed to identify weather the information they are requesting is correct by clicking a “YES” button. What I need that button to do is populate that same data from row R5:AC5 to a “Queue” list in row E24:Q24 (I was able to do this part easily with a macro and form button)…Here’s where I am stumped…. When the user presses another button that auto clears the input cell and allows them to input another number, they would click the “YES” button confirming it’s accuracy again AND THEN autopopulate in the next row of the “Queue” list in row E25:Q25, and so on to E26:Q26, E27:Q27, etc…..
I’m thinking some VB code in an ActiveX button but I know zip about VB and need some coding help unless someone has a wiser approach…
Thanks!
JayKai
Comment from Jon Peltier
Time: Friday, December 23, 2011, 12:47 pm
Jaime -
This post is about buttons and other controls, not the complex tasks that can be assigned to them.
What you should do is neaten up your problem statement, maybe break it into a few smaller pieces, and review them. Then if you need further help, go to MrExcel.com and post your questions.
Comment from Simon Cheng
Time: Thursday, January 26, 2012, 3:00 pm
Great post. Love it.
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.