Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Controls > Insert; how
Deze pagina in het Nederlands

Using controls on worksheets

Where to find the controls

Excel 2007 and up

In Excel 2007 and later versions, the form controls and control toolbox controls are slightly hidden. First of all, you need to show the "Developer" tab of the ribbon. Here is how that's done:

Excel 97-2003

In older Excel versions the controls are housed on two toolbars; the "Forms" toolbar and the "Control toolbox" toolbar. You can show both toolbars using the menu "View", "Toolbars":

 Werkbalken met besturingselementen tonen

Fig. 3: Access to the controls toolbars in Excel 97-2003.

After turning on these two toolbars they are shown:

Werkbalken met besturingselementen

Fig. 4: The control toolbars in Excel 97-2003.



All comments about this page:

Comment by: kanwaljit (29-12-2009 07:48:20)

Hi Jan,

Excellent article to say the least. Very helpful for an average excel user like me. Wish I could download in pdf, so as to get a print and read whenever I like.

Thanks a lot !


Comment by: Jeff Page (31-1-2010 09:23:03)

As a person who routinely prints articles for reading and/or filing, it is hindered when articles are spread over multiple pages. An option for a printer-friendly version (like, would also allow someone to save to PDF the entire article.

Thanks. Great site.


Comment by: Jan Karel Pieterse (31-1-2010 10:51:43)

Hi Jeff,

Thanks you for your comment.

I can see the benefit of having all on one page in case you want to print it.
In general, web experts often advise NOT to make pages on websites very long, but rather spread them across multiple pages. This of course conflicts with the ease of printing an entire article.


Comment by: Del (13-5-2011 10:27:20)

Is there a way to customize the Excel 2010 toolbar so that it duplicates the toolbar used in Excel 2003 version?

Learning how this "ribbon" works is quite confusing to someone who has used Excel 2003 for years. Just to do a sort is a challenge.

Looking forward to your response for a solution to my problem.

Best regards,


Comment by: Jan Karel Pieterse (15-5-2011 03:21:48)

Hi Del,

You cannot duplicate Excel 2003 toolbars in 2007. The closest option is to use the QAT.
There are third party tools that mimick the 2003 interface, but I'd suggest you to keep using the ribbon, you'll get used to it. It has its quirks, but so did the 2003 UI.
What helps is that most 2003 menu shortcuts still work.


Comment by: Nikhil Dwivedi (27-3-2012 23:46:22)

Thanks, I was confused with Control Toolbox but not now.


Comment by: SpreadSheetNinja (26-9-2012 15:54:08)

It continues to amaze me how microsoft is driveing away from the whole vba in excel.. well if you think about it, its usualy allways hidden on install (aka developer mode).
Office 365 removed it completely.
64-bit office suffer, some vba code or functions cant be used at all (im stuck whit 64...)

They removed calendar picker from the vba list... (and the copy from older office doesnt work due 64bit in my case, and even if it worked it would have to be done on every computer that uses it...)

VBA adds SO much to excel.. dunno how i can live whiout writeing my own functions sometimes..

Soon office excel will simply BE a spreadsheet whit no other functions but what microsoft office crew bothers to put inn.. and considering theyr removeing even features there, and never bother to fix the weeknum feature to display the actual correct week


Comment by: Jan Karel Pieterse (26-9-2012 19:13:47)

Hi SpreadsheetNinja,

I cannot relate.

Office 365 DOES include VBA, VBA just does not work when editing in the browser. For obvious (security and performance) reasons.

Although MSFT no longer update the VBA and the VBA Editor, the Excel object model is being kept up to date.

What function(s) have you discovered that do not work in 64 bit Excel?

NB: Excel 2013 now includes an ISO weeknumber function.


Comment by: Praveen Kumar Reddy Chinta (26-11-2012 07:52:45)

Nice Post !



Comment by: Kenny Ng (4-4-2013 10:14:36)

I linked a cell(with formula sum of 1,234)on workbook to an activex textbox.
Numeric number shown in the textbox are example 1234.122222.When i change the numeric number to 1,234 in the textbox properties it in turn erase the sum formula in my workbook cell.
Is there any way the textbox will shown 1,234(thousand separators) when it is linked to the workbook cell.


Comment by: Jan Karel Pieterse (4-4-2013 13:07:20)

Hi Kenny,

One way would be to use an extra cell to use as the linked cell, in which you use the TEXT function to format the result the way you need.


Comment by: Bill Savage (23-4-2015 18:39:36)

I have Excel 2013 and 2010 on two PCs at home. I can use VBA code to display a calendar in a date field.

At one of my customers with 2010 or 2013 on different PCs, Excel calendar will not work. I do not have either admin privileges nor the ability to modify the existing install of MS Office.

I have assured that the file is saved in macro enabling mode, attempted tweaking the VBA code, and searched MS and other websites for some help to no avail.

(I am too far removed from my programming days, i.e. > 20 years, to feel comfortable with VBA)

I would like, (1) to have the calendar appear when the user clicks in a date cell, or (2, better) have an interactive calendar apply a date in the first cell of a range. Even better, an interactive calendar apply to any cell in a range.


Comment by: Bill Savage (23-4-2015 18:41:08)

The following was copied from
Code goes here
Private Sub Calendar1_Click()
    ActiveCell.Value = CDbl(Calendar1.Value)
    ActiveCell.NumberFormat = "mm/dd/yyyy"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
        Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
        Calendar1.Top = Target.Top + Target.Height
        Calendar1.Visible = True
        ' select Today's date in the Calendar
        Calendar1.Value = Date
    ElseIf Calendar1.Visible Then Calendar1.Visible = False
    End If
End Sub

If you select a cell in the Range A1:A20 the Calendar will popup and when you Click on the calendar the Date will be placed in the active cell. If you select a cell outside the range the Calendar will disappear.

Note: You can use this if your range is not one area
If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then

Tip 1: If the cell value is a Date and you want that the Calendar popup with that date selected you can replace this line Calendar1.Value = Date for

        If Not IsDate(Target.Value) Then
            Calendar1.Value = Date
            Calendar1.Value = Target.Value
        End If

Any help or suggestions will be greatly appreciated.


Comment by: Alan Elston (24-12-2017 08:57:57)

I finally found out here how to add a Form or ActiveX button in Excel 2003. There were lots of references for 2007+ , but this is the only clear explanation that I could find for Excel 2003. Thankyou.
( I note also that if you select the View Tab then select the Visual Basic tool bar, you can select the tools controls icon to get the ActiveX controls Tool bar. ) .


Comment by: Sascha (30-7-2019 01:53:00)

I need to learn how to create a tab e.g. setup or configure to define some topics with predefined data/texts so from other sheets I can list and pick these data/texts in a drop down list and choose one.
Where can I learn about this?
Thanks in advance


Comment by: Jan Karel Pieterse (30-7-2019 11:32:00)

Hi Sascha,

I would consider using Data, Validation for this purpose. See:


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.