Subscribe
in a reader
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:
- Click the Office button and select "Excel options..." (for Excel 2010 and up: Click File, Options and then locate this option on the "Customize ribbon" tab).
- Click the "Popular" tab and check the box next to "Show Developer tab in the ribbon":
Fig 1: Showing the Developer tab on the ribbon (Excel 2010 and up).
- After you've checked the box and clicked OK, you will find a new tab called "Developer" on your ribbon. This tab houses a group called "Controls", which in turn contains a button "Insert". The dropdown list shows all available controls:
Fig. 2: The controls on the Developer tab.
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":
Fig. 3: Access to the controls toolbars in Excel 97-2003.
After turning on these two toolbars they are shown:
Fig. 4: The control toolbars in Excel 97-2003.
Comments
Showing last 8 comments of 13 in total (Show All Comments):Comment by: Nikhil Dwivedi (3/27/2012 11:46:22 PM)
Comment by: SpreadSheetNinja (9/26/2012 3:54:08 PM)
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 (9/26/2012 7:13:47 PM)
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 (11/26/2012 7:52:45 AM)
Cheers.
Comment by: Kenny Ng (4/4/2013 10:14:36 AM)
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.
Thanks
Comment by: Jan Karel Pieterse (4/4/2013 1:07:20 PM)
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 (4/23/2015 6:39:36 PM)
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 (4/23/2015 6:41:08 PM)
Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
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
Else
Calendar1.Value = Target.Value
End If
Any help or suggestions will be greatly appreciated.
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: www.eileenslounge.com.