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.
Edgar Hungers says
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
Jon Peltier says
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.
Edgar Hungers says
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
Jon Peltier says
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.
Edgar Hungers says
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
Edgar Hungers says
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.
Jon Peltier says
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.
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
james says
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
Jon Peltier says
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:
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.
Erich says
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…
Jon Peltier says
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.
Erich says
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
Pete Kies says
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.
Jon Peltier says
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.
Mike says
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.
Jon Peltier says
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.
Addy says
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.
Steve says
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?
Jon Peltier says
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.
Steve says
Unfortunately, I have to use Excel 2003 until next year some time. The controls were created in Excel 2003.
Jon Peltier says
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.
Steve says
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?
Mark says
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?
Jon Peltier says
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.
ashley says
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..
Jon Peltier says
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.
Laura says
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?
Jon Peltier says
Laura –
If you’ve inserted an ActiveX Controls Toolbox button, the auto-generated code should say:
In between you need to insert a line like
Then you have to click to unhighlight the Design Mode button in the Developer tab. ActiveX controls do not work in Design Mode.
Lee Deverick says
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
Jon Peltier says
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.
Jaime says
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
Jon Peltier says
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.
Simon Cheng says
Great post. Love it.
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.
Jon Peltier says
Right click on the ActiveX control, and choose Properties. Or use a Form control.
wf says
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.
Jon Peltier says
ActiveX “contemporary” heh heh heh. Maybe compared to the old school forms controls…
Stefan says
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?
Jon Peltier says
Stefan –
I just did a quick and dirty test, and changing the linked cell did cause the _Change event to fire.
Sondra says
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
Jon Peltier says
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.
Anonymous says
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?
Jon Peltier says
ActiveX or Form dropdown?
Is it linked to a cell?
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?
Jon Peltier says
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).
Cool Blue says
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?
Jon Peltier says
While shapes have all the gratuitous formatting introduced in Office 2007, the Form Controls have retained their barebones formatting options from Classic Excel.
Steven C says
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
Jon Peltier says
Data validation does not necessarily prohibit multiple use of items in a list.
Chaity Podder says
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
Jon Peltier says
What’s an “insert box”? Why do you need thousands of them?
Jeffrey Smith says
Hi Jon,
Hope you are still monitoring this page and can assist with this. In an Excel 2010 worksheet, I have a Forms Control Button that when clicked * sometimes * places the cursor at the beginning of the Text in the Button instead of executing the underlying macro. Clicking off the Button into a worksheet cell and then clicking the Button again will then execute the macro. Other times, the first click will execute the macro as intended. The macro loads a User Form. Can you advise how to fix this behavior?
Jon Peltier says
Jeffrey –
This is strange behavior. If the worksheet was originally created and the button in Excel 2003 or earlier, and then the file was saved using the new file format introduced in Excel 2007, I would not really be surprised, because there has been a lot of funny behavior of legacy workbooks opened in new Excel versions.
If the workbook was created in Excel 2007 or later, this is the kind of behavior that might need a repair reinstallation of Office.
Jeffrey Smith says
Thanks for your advice, Jon.
It was built entirely in 2010 so I’ll proceed with an Office Repair and see if that helps (although I just did a fresh install of Office 2010 Pro (on a new hard drive with a cleans install of Windows 7 about 2 months ago) so I’d be surprised if that got corrupted so quickly (although I’ve been in some pretty heavy development since then so who knows). I’ll give it a shot.
Thanks again.
Jon Peltier says
It might also be something else with the file. Is it just one file that has this behavior?
Jeffrey Smith says
Yes, Jon. It’s only the one file I’ve seen exhibit this behavior. I just ran AppsPro Code Cleaner on the file and, at least for now, it seems that cured the problem.
chuck johnson says
Hello Jon – Two questions:
I have an Active X list box with six macros callable via Select Case method. Five of the six work fine. One doesn’t execute.
Thoughts on why?
So, I created a Form list box which works with all six macros. However, the right side of the Form list box shows a scroll
bar outline that I cannot eliminate. Suggestions?
Jon Peltier says
Chuck –
In general, the forms listbox will be less prone to strange behavior.
With the ActiveX listbox, make sure that the list items and the select case items are all spelled correctly.
The forms listbox always shows the scroll bar, even if the list isn’t long enough to need one.
Geoff Knowles says
I recently posted this on StackOverFlow without response so far.
On a wsheet amongst a number of other ActiveX controls I have a textbox and 2 images. They are used for a search function. As you would expect the textbox is for user entry and the images are for ‘run search’ and ‘erase search’. I set the search text as a string.
My issue is when hitting either ‘run search’ OR ‘erase search’ the textbox momentarily changes to show the previous text string. I have set this previous string to “” all over but without success.
The sequence, knowing the search will fail, is…
1) Enter ‘XXXX’
2) Hit ‘run search’, code executes correctly
4) Prior to the textbox returning “XXXX not found” it momentarily shows the previous entry, say “AAAA”, before returning the correct result.
Is this an ActiveX quirk or can it be prevented?
Office 365 and Win10
Jon Peltier says
Geoff –
Do you have a link to the StackOverflow post? Alternatively, can you share the code that leads to this glitch?
Jon Peltier says
Geoff –
I just tried a little test. I put a textbox and commandbutton on a worksheet, and used this code:
I noticed that when I enter something new in the textbox, then click the button, the textbox flashes on “Finished” briefly before entering the countdown.
Then I noticed that it seems related not to my code at all, but to the textbox losing focus. When I enter something new in the textbox, then select a cell, the previous textbox contents appear for an instant. No need to click the button or run any code.
It looks like an ActiveX thing, and I guess you’re stuck with it.
Geoff Knowles says
Jon
Thank you for the response and your investigation. I also find when entering text then clicking any cell the previous entry is shown briefly.
I use a Private Sub txtSearchInput_LostFocus() event to trap user error but cannot think of a way to trap this phenomenom so as you suggest ‘I am stuck with it’.
BTW, your word ‘LostFocus’ caused a flashback to a private dialog with Tom Ogilvy many years back on some Excel forum. An MVP as you will be aware and an inspirational man of great authority on VBA, I found myself discussing the strange behaviour of textboxes within frames. Albeit vaguely I think LostFocus was covered. Forms controls of course but it seems ActiveX textboxes have issues too.
Again many thanks for your prompt response.