Forms Controls and ActiveX Controls in Excel

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
Forms Toolbar in Excel

ActiveX Controls
ActiveX Controls

VBA Controls
VBA Controls Toolbox

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.

Assign Macro to Forms Button

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.

Peltier Tech Chart Utility

Comments

  1. 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

  2. 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.

  3. 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

  4. 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.

  5. 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

  6. 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.

  7. 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.

  8. Edgar Hungers says:

    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

  9. 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

  10. 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 Sub

    It 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.

  11. 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…

  12. 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.

  13. 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.

    http://support.microsoft.com/kb/102552

  14. 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.

  15. 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.

  16. 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.

  17. 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.

  18. 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.

  19. 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?

  20. 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.

  21. Unfortunately, I have to use Excel 2003 until next year some time. The controls were created in Excel 2003.

  22. 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.

  23. 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?

  24. 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?

  25. 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.

  26. 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..

  27. 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.

  28. 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?

  29. Laura -

    If you’ve inserted an ActiveX Controls Toolbox button, the auto-generated code should say:

    Private Sub CommandButton1_Click()
    
    End Sub

    In between you need to insert a line like

        Sheets("My Sheet").Activate

    Then you have to click to unhighlight the Design Mode button in the Developer tab. ActiveX controls do not work in Design Mode.

  30. 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

  31. 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.

  32. 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

  33. 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.

  34. Great post. Love it.

  35. dumisani khosa says:

    hi
    i”m having troouble with my excel 2007. when i insert combo box (ActiveX control), and go to format control to add the data i need in my drop down. the panel only have size, protection, properties and web. so as a result i cant add my drop down list. can you help if there’s another way i can add the list.

  36. Right click on the ActiveX control, and choose Properties. Or use a Form control.

  37. Despite their Stone Age look I opt for Form controls over ActiveX controls which to date (and to my limited knowledge) still have occasional format bugging e.g .re-sizing font issues. Complex use or sharing files to a wider user community this is pain for which explaining the unexplainable nor using VBA font controlling workarounds weigh up to giving up on the more contemporary formatting options of ActiveX. MS developers should spend more time reading these kind of blogs iso of dreaming up ivory tower functionalities which they reckon will revolutionize the user experience but in fact are the first thing an intensive user looks for how to turn off.

  38. ActiveX “contemporary” heh heh heh. Maybe compared to the old school forms controls…

  39. Hi Jon,

    Good article. I was wondering if the following behaviour is as expected: I have an ActiveX scrollbar that is linked to a cell. When I click on the arrows of the scrollbar to change its value the scrollbar_change event fires and I have code that performs a few tasks. However, when I change the value directly in the linked cell, the scrollbar value updates but the scrollbar_change event does not fire. Is that how it is supposed to work, or is it something that will be related just to my specific environment?

  40. Stefan -
    I just did a quick and dirty test, and changing the linked cell did cause the _Change event to fire.

  41. Hey Jon,
    Hoping you can help. Let me say I am not a techie and I’m a low level Excel user. I am attempting to move a form we use from Word to Excel and use some Excel features to make it simpler. Using 2010 version. I’m able to do everything except use the text box form field in Excel. We use them in the Word document to tab through the fields in the form and enter data (random data, nothing I can use a list for). I used your info above to set up Active X text boxes but it doesn’t accept any of the data I enter. I get a reference not valid and have to end task Excel to get out of it. Is there anything I can do that would replicate what we use in Word?
    Any help is appreciated.
    Thanks!
    Sondra

  42. Is the Word form built right in a document? You could put all of those controls into a worksheet, but it won’t be easy to navigate with tabs, and it will just be awkward.

    It’s more common in Excel to build userforms that act as dialogs. Then you’ll need code to dump the data into a workbook.

  43. I have a drop down box set up. I can select the option I want. Then I save it. When i reopen it, the selection has been cleared. Anyways to stop that from happening?

  44. ActiveX or Form dropdown?
    Is it linked to a cell?

  45. Jacob KB (DK) says:

    I have a question which I’m not sure whether you can answer but here we go.

    At the beginning of this article you mention the problem of ActiveX not being compatible for a macExcel.
    Do you know whether this problem also is the reason for why i can’t find the “Additional Controls” option in my userform toolbox? (On Windows i know you can just right-click, not an option on mac apparently)
    - My problem is that i don’t know whether these two problems are related, and it’s difficult for me to attain knowledge on this subject, because I’m not able to use the activeX feature at all. So i suppose the real question is, can you find Additional Controls on a mac?

  46. Jacob -

    I don’t think it’s an ActiveX issue, but I think it’s similar in that the capabilities of Mac Office have not been developed sufficiently to allow the use of ActiveX controls or additional controls (which in Windows are other software libraries installed on the computer).

  47. Hi, I’m confused by this statement:
    “ActiveX controls have a scaled back Format Control dialog, but a rich formatting environment offered by the Properties window.”
    Its seems like the form controls have far richer formatting options eg msoShapeRoundedRectangle, Shadow, Relection, Glow, 3-D Format…
    Am I missing something?

  48. While shapes have all the gratuitous formatting introduced in Office 2007, the Form Controls have retained their barebones formatting options from Classic Excel.

  49. Nice Demo will help in many data entry issues but a query:
    When using the “data validation” method of data entry which maybe preferred, what if the data entered is not to be uniquely one time entered but may require multiple same responses while filling in the form and want to choose from the predefined list of choices such as a products condition of Good, Bad, In the Shop, Returned etc. and going down the list of items and want to use a dropdown menu list to choose the attribute for the items condition but DATA Validation stops using the data from the list more than once due to Data Validations rules. But don’t want those rules to effect use of the data in non unique lists. How is that done in Excel 2007 and 2010? I like using the Data Validtion method of entry but don’t like it is taking away choices when the list should not be unique one time used data?

    Thanks Steven

  50. Data validation does not necessarily prohibit multiple use of items in a list.

  51. Hello,
    I am facing a big trouble with insert box. I am using Microsoft excel 2010. In my excel file, I am preparing a database file & I have putted more than thousand boxes for 6 columns. Now my excel file getting slow for opening, editing etc for any kind of work & the file size increase also. It’s not swifter then other excel file where I didn’t put any ” checkbox”
    Would you please advise, how do I cover come this problem.

    Thanks

  52. What’s an “insert box”? Why do you need thousands of them?

Subscribe without commenting

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.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites