Using controls on worksheets

Content

Introduction

As a frequent spreadsheet user, you will be familiar with the concept of using data entry cells to feed your model with input.

To ease data input, you can use the Data, Validation option (which is NOT discussed in this article).

Sometimes it is more convenient to be able to change the input of your model by using controls you can interact with using e.g. your mouse. Excel offers a range of controls to do this, such as dropdown lists, spinner buttons, option buttons and the like. These user interface elements are called controls -after all, they allow you to "control" Excel-.

This article shows you how you use the form and control toolbox (ActiveX) controls.

Where to find the controls

Showing the Developer tab on your ribbon

Making sure the Developer tab shows up is quite easy

  • For Excel 2010 and up: Click File, Options and click the Customize Ribbon tab
  • Check the box indicated below and click OK:
  • Displaying Developer tab

    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:
    Besturingselementen in het lint

    Fig. 2: The controls on the Developer tab.

Note that there are two groups of controls:

  • Form Controls
    These are the most reliable controls, but they do not offer much flexibility regarding formatting.
  • ActiveX Controls
    These are more versatile, but also less reliable. They have the annoying tendency to move around and change size without your consent.

Two groups of controls; the differences

By now it is apparent that there are two distinct series of controls: Those from the forms group and those from the Control toolbox (named ActiveX controls in Excel 2007 and up).

Pro's and con's

The table below lists some advantages and disadvantages of both control sets:

Control type
Form controls
ActiveX controls
Advantages
  • Simple to use
  • Can be used on chart sheets
  • Assigning control to a macro is simple
  • Little known problems
  • Lots of options
  • Lots of events (VBA)
  • Lots of formatting options
  • Lists return the selected value rather than the index number
Disadvantages
  • Lists return the index number rather than the selected value
  • Cumbersome to use one macro for multiple controls
  • Sometimes cause of trouble with file corruptions

Which should you use

By now you'll be wondering which set you should use. Generally speaking, I recommend using the controls from the forms toolbar. If you have specific needs regarding formatting which cannot be achieved using the forms controls (or if you want to program events in VBA), then you'll have to switch to the ActiveX controls (control toolbox controls).

Inserting controls

Inserting a control on your sheet is very simple: Just click the control you need and drag a rectangle on the sheet at the position where you want the control to appear. You can also just click on the sheet and have Excel decide what dimensions to use for the control.

If you hold down the alt key when you click on the worksheet, then the control will be aligned to the cell grid. You can also hold the alt key when you are dragging the control or resizing the control to have it snap to the grid. This is a quick way to ensure your controls are nicely aligned and of equal size.

Double click a control on the toolbar or on the Insert controls dropdown if you want to draw multiple copies of that control. Click that control again (or any other control) to get out of that mode.


Overview of the available controls

The table below shows which controls there are and describes each one shortly.

Control name
Control use and remarks
Label
Add a label next to other controls.
Frame
Use this control to group other controls. OptionButtons placed within a frame work together.
Button (CommandButton)
Start a macro
CheckBox
Set an option, Select multiple options from a list of options.
OptionButton
Select one option from a (short) list.
ListBox
Select an option from a list. Multiple options are visible at the same time.
ComboBox
Select an option from a list, only the selected option is visible.
ScrollBar
Quickly change numeric values.
Spinner
Change values step-by-step easily.
TextBox
Enter a text.
ToggleButton
Toggle status. This control is not recommended, I advise to use either a checkbox or a set of two OptionButtons.

Detailed description of the controls

Label

The label control is the simplest control available, all it can be used for is to display descriptive text. Use this control if you want to add some explanatory text to another control.

TIP: You can make sure the label text is derived from a worksheet cell. To do so, select the label and then click in the formula bar and enter a reference to the cell. See fig. 5.

Label tekst hangt af van celwaarde

Fig 5: The text on a label drawn from a worksheet cell

Frame

You can use a frame to visually group controls with a shared purpose. Apart from that, the frame control has a specific function for option button controls (see the appropriate section about them). You must start with drawing the frame control before adding the controls you want placed "inside" the frame. To make this a painless process, start out by drawing a relatively large frame (you can make the frame smaller later on). After that, draw the controls inside the frame:

Een groepsvak met drie keuzerondjes

Fig 6: Frames with OptionButtons within.

Button (CommandButton)

Buttons or CommandButtons are used to start VBA code (macro's). If you draw a Button from the Forms toolbar on a sheet, Excel will prompt you for a macro to run when the button is clicked (fig 7). If you have not written a macro yet, then you can type the macro's name and click the "New" button to have the (empty) subroutine created for you:

Een macro toewijzen aan een knop

Fig 7: Excel asks you what macro to run when the button is clicked.

If you used the CommandButton from the Control toolbox, you need to double-click the button (in design mode) to access its VBA click event. Code for control toolbox (ActiveX) controls is typically written in the code module behind the sheet they are placed on.

TIP: If you want to change the properties of a control from the control toolbox (an ActiveX control), then you must put your sheet into "Design mode". You can find this button on the "Developer" tab, within the "Controls" group. When you want to start using the controls, click the same button to get out of design mode.

CheckBox

Typically, a checkbox control is used to enable the user to turn something on or off, or to answer a yes/no question. The checkbox always stands on its own, allowing multiple options to be selected (checked) at the same time.

OptionButton

The option button is very similar to the check box, but only allows mutually exclusive choices to be made; in a set of option buttons, only one option can be "checked". If you take no specific action then all option buttons on one sheet will be treated as one single group. It is possible to have multiple groups on a sheet. The method to achieve this differs between the two sets of option buttons.

The method to tie an option button to a cell differs between the two types too.

Forms option button

To group option buttons from the forms toolbar, first draw frames on your sheet. Then draw the option buttons INSIDE the frame:

Keuzerondjes in groepsvak

Fig 8 : Two frames with option buttons.

Option buttons from the forms toolbar share their linked cell. The value returned to the cell is the index number of the selected option. Note that the index will match the order in which you created the option buttons.

To select a control from the forms toolbar (for example to be able to move the control), either right-click the control or control+click it. To select multiple controls to change their properties in one go, hold control while clicking them.

Group controls you want to keep together, for example those within a frame, by control+clicking them in turn and then right-clicking on one of their edges and selecting "Grouping", "Group".

Control toolbox option button (ActiveX)

The option button from the Control toolbox toolbar (ActiveX) has a special property to set up which work together, called the Groupname property:

Groupname instellen

Fig 9: Groupname property of the ActiveX OptionButton control.

If you do not change this property, all option buttons on a sheet work together as a single group.

ActiveX option buttons all have their own linked cell, which will receive the checked state of their parents as a True/False value.

TIP: You can get at the properties window of the controls by right-clicking a control and selecting "Properties". You can also click the appropriate button on the Control Toolbox toolbar. In Excel 2007 and up, you'll find that button on the Developer tab, within the Controls group.

ListBox

If you want to enable your user to select an option from a list you can use a list box. Of course you could also use a set of option buttons for this goal, but making a set of option buttons dynamic (for example if you want to be able to expand the number of choices) is cumbersome. If you have a limited set of available choices (rule of thumb: no more than 5), use option buttons. If there are more, use a list box or a combo box control.

You can either have the control pick up the list from a range of cells, or add the choices to the list box control using VBA. This is done by entering the corresponding information into the ListFillRange or Input range property. If your list of choices resides on a different worksheet from the one your list box is placed on, you must define a range name for the list. Do so by selecting the list and hitting control+F3. In Excel 2007 and up you then need to click the "Add" button. Enter a name for the list and click OK until you're back in Excel. After that, you can enter this new range name in the appropriate property of the control.

The second most important property is the LinkedCell (cell link), this cell will receive the result of selecting an item in the list box:

Opties van de keuzelijst

Fig 10, Two important propeties of the ActiveX list box control.

Opties keuzelijst formulier

Fig 11: Two important properties of the forms list box.

Note that the list box from the forms toolbar will show the index of the chosen item in the cell, whereas the control from the control toolbox returns the actual value to the cell. For the list box from the forms toolbar, use a formula like this one to get the actual value:

=INDEX(ListForComboAndList,C1)

You can set the list box to "Multi" or "Extended" to enable multiple selections. In that case, the linked cell will show either a zero for the list box from the forms toolbar or #N/A for the control toolbox list box control. You will have to use VBA to read what items have been selected and act accordingly.

ComboBox

A combobox is very useful when there are many values to choose from and when you only want to show the chosen item. With the combobox from the Control toolbox you can dynamically add items to the list -using VBA- when the user types a new item in the box instead of selecting an existing item. The combo box form the forms toolbar does not have this possibility: the user is limited to the choices available in the list.

The two important properties LinkedCell and ListFillRange operate in the exact same way as for the list box control.

ScrollBar

The most important reason to use a scrollbar is to be able to change a value very quickly. The user can drag the scroll button, click next to the scroll button to change "page" or click the arrows to increase or decrease the value stepwise.

Schuifbalk met gekoppelde cel

Fig 12: Scrollbar with linked cell.

A vertically placed scroll bar works opposite compared to a spinner control. Clicking the up arrow on a spinner increases the value; clicking the up arrow on a scroll bar decreases the value. In my opinion, the former is more intuitive. If I need this vertical setup, I prefer to use a spinner over a scroll bar.

A scroll bar enables you to change the value in two ways. The "Incremental change" is performed by clicking the arrow buttons, the "Page change" is performed by clicking next to the scroll button. See fig. 13:

OP 2 niveau's instelbare stapgrootte

Fig 13, Two levels of step sizes.

These properties have a different name for the scroll bar of the Control Toolbox toolbar (ActiveX) "SmallChange" and "LargeChange" respectively, see figure 14:

SmallChange en LargeChange eigenschappen

Fig 14, Setting properties of the ActiveX controls scroll bar.

Scroll bars can only work with integers. The range you can use differs between the two families. The forms control ranges from 0 to 30,000. The ActiveX control can go as high as 666,666.

If you need steps less than 1, then use a calculation. For example if you need a step size of 0.5, divide the linked cell's value by 2.

Spinner

If you want to be able to quickly change a cell value stepwise, the spinner is the place to be.

Een kringveld

Fig 15: Spinner controls.

Setting up spinner controls works identical to the scroll bar control, using the same properties. Of course the spinner does not have a Page change (LargeChange) property.

TextBox

There is only one text box control, member of the ActiveX family of controls. I find this control to have little use, because you can simply use a cell and enter text into the cell directly.

ToggleButton

The last control I discuss here is the toggle button. This is another example of a control that is only available through the ActiveX family of controls. In my opinion, this control is ambiguous. It could be used to either indicate an action, or a state. You could use a control like this to change the page setup of a sheet from portrait to landscape and vice versa. Disadvantage of this control is that the state and action paradigm are conflicting, especially if the two states have a different name (like in the example).

Suppose you want to enable toggling between portrait en landscape. You might be tempted to use a toggle button, which has some VBA attached to it to set the option and which updates the caption of the control. What does the caption indicate, the current status, or the status AFTER clicking the toggle button?

Wisselknop; Onduidelijk wat de knop aangeeft

Fig 16: Ambiguity when using a toggle button: which one indicates we're in Portrait mode?

Due to this ambiguity a toggle button is only useful to indicate an on or off state for a property which has the same name in both states. For this goal, a checkbox is to be preferred. If there are two mutually exclusive choices, consider using two option buttons.

Interaction between controls and worksheet cells

Each control can be tied to a cell and thus (by using that cell in your formulas) affect your spreadsheet model directly and drive interactivity between your model and the user. For the forms controls, you access these options by right-clicking  the control in question and selecting "Format control". Each control offers its own set of options, which are located on the "Control" tab. (see figures 11 en 13). Similarly, you can change the properties of the Control toolbox (ActiveX) controls using their properties window.

Conclusion

Excel is a very flexible instrument to perform analyses and what-if scenario’s. You use formulas in cells with one or more input cells to calculate the various situations. To ease working with different values and/or choices, you can put the controls from either the Control toolbox or the Forms toolbar to good use. Proper use of these controls make your models easier to use.

The controls also enable you to ease data entry and at the same time improve data quality by minimizing the risk of wrong entries. For "day-to-day" use, I recommend the Forms controls. If there are specific options you need which are not offered by the form controls then you can also implement the Control toolbox (ActiveX) controls.

 


Comments

All comments about this page:


Comment by: kanwaljit (29-12-2009 07:48:20) deeplink to this comment

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 !
Regards
Kanwalijt


Comment by: burnout (30-12-2009 12:56:35) deeplink to this comment

spelling in form control advantages:
"Assigning contorl to a macro is simple"


Comment by: Jan Karel Pieterse (31-12-2009 04:03:46) deeplink to this comment

Hi Burnout,

Good catch, fixed. Thanks!


Comment by: Divyakanth (11-1-2010 00:17:08) deeplink to this comment

Hello,

When you add a control to a worksheet say a check box, is it possible to get the corresponding rows on which it is placed?

OR

Is it possible to bind a control to certain no. of rows so that you will be able to know the related rows ?

Thanks for your time in advance.


Comment by: Jan Karel Pieterse (11-1-2010 01:29:57) deeplink to this comment

Hi Divyakanth,

The TopLeftCell property of a control returns the cell which is below the top-left corner of your control. So:

Sub Example()
    MsgBox "Your Control is on top of cell: " & ActiveSheet.CheckBoxes("Check Box 1").TopLeftCell.Address
End Sub


Comment by: Divyakanth (11-1-2010 04:15:09) deeplink to this comment

Thank you very much for your swift response. I was infact in the process of removing the checkboxes. Your response saved them ;-)


Comment by: Jeff Page (31-1-2010 09:23:03) deeplink to this comment

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 PCMag.com), 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) deeplink to this comment

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: John Tang (23-1-2011 14:17:42) deeplink to this comment

I tried out Tip No.2 to draw multiple copies of a selected form control but found out that it does not work. I use Excel 2007 version. Appreciate your clarification.


Comment by: Jan Karel Pieterse (23-1-2011 22:48:35) deeplink to this comment

Hi John,

You're right, as of Excel 2007, you can no longer double-click a control in the toolbox so you can draw more of them without having to go to the ribbon again. Pity! I guess the fastest way around this new limitation is by copying and pasting them.


Comment by: Paul Simpson (14-3-2011 10:00:25) deeplink to this comment

So change the Caption of your ToggleButton based on the state. Good-bye ambiguity.


If tglButton Then
     tglButton.Caption = I'm ON now"
Else
     tglButton.Caption = "OFF again"
End If


Comment by: Jan Karel Pieterse (14-3-2011 10:36:28) deeplink to this comment

Hi Paul,

I can't say I agree.
Though this means the toggle button's caption reflects its state, it now no longer tells the user what happens if you push it. Two option buttons are a better way to show both state and how to change that state if you ask me.


Comment by: Del (13-5-2011 10:27:20) deeplink to this comment

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,
Del


Comment by: Jan Karel Pieterse (15-5-2011 03:21:48) deeplink to this comment

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: Mike Drummond (15-5-2011 12:08:51) deeplink to this comment

Thanks for the controls info.
Is there a known problem with Form controls and 2010?
I'm finding that Forms Combo box will very often crash Excel 2010. Error is "Excel is restarting"

If I type the list index directly into the Cell Link cell there is no problem.

I tried deleting the combo control and inserting a new one.

The ActiveX combo doesn't have a problem.


Comment by: Jan Karel Pieterse (15-5-2011 21:12:20) deeplink to this comment

Hi Mike,

So far I've seen more trouble with the ActiveX ones than with the forms ones.Which doesn't fix your problem of course.
Does it also crash when you insert a new sheet and put the combo on that sheet?
Hunch: clean up your temp folders.


Comment by: Mike Drummond (16-5-2011 02:23:48) deeplink to this comment

SOLVED.
I had UDFs to return a chart axis Max & Min to the worksheet which are Application.Volatile (otherwise they weren't updating). Disabling A.V solved the crashing.

Thanks for your response because it got me searching for non-control reasons. Mike


Comment by: adam (1-7-2011 01:37:07) deeplink to this comment

Hello,
I have a sheet that uses a scrollbar linked to a graph, which I use to show students how the data changes between set points.

I get tired of holding the scrollbar down to 'play' the 'animation'. Is there a play control button? Could a scrollbar be tweeked so that you only have to click the button once and it just goes through to the end?

That would be very nice. Any ideas?

thx


Comment by: Jan Karel Pieterse (4-7-2011 01:39:12) deeplink to this comment

The easiest way would be to use a little bit of VBA code that increments the cell which holds the scrollbar's result. Suppose it is cell A1 on sheet1:


Sub RunChart()
    dim lCt as Long
    Dim lWait as Long
    For lCt=1 to 1000
        Worksheets("Sheet1").Range("A1").Value=lCt
        For lWait=1 to 10000 'Change this number to increase/decrease speed
        Next
    Next
End Sub


Comment by: adam samuelson (10-7-2011 02:12:55) deeplink to this comment

Hi Jan,
Thankyou for your prompt reply, the code you provided worked very well on the data and is exactly the type of thing I wanted.

The problem I have now is the graph doesn't 'refresh' with each data change, it only changes when the macro finishes running.

Interesting to note that when I slide the scrollbar (slowly) the graph does change, so I suspect it is a refresh rate problem.

Any ideas as to how I could solve this new problem??

Cheers


Comment by: Jan Karel Pieterse (11-7-2011 00:24:05) deeplink to this comment

Hi Adam,

Just before the waiting loop, include this statement:

DoEvents


Comment by: James Wagner (6-1-2012 08:52:45) deeplink to this comment

I am trying to link a checkbox to multiple sheets in a workbook so that checking the checkbox on sheet one, checks the corresponding box on sheets 2, 3, and so on. Is this even possible?

Thank you


Comment by: Jan Karel Pieterse (9-1-2012 01:05:45) deeplink to this comment

Hi James,

If you make sure all checkboxes share the same linked cell, they should work as a team.


Comment by: Nikhil Dwivedi (27-3-2012 23:46:22) deeplink to this comment

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


Comment by: SpreadSheetNinja (26-9-2012 15:54:08) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

Nice Post !

Cheers.


Comment by: Kenny Ng (4-4-2013 10:14:36) deeplink to this comment

Hi,
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 13:07:20) deeplink to this comment

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: Tucker (20-7-2013 23:43:18) deeplink to this comment

I have the scroll bar in place, I just can't figure out how to connect it to the data/script (i.e. I scroll the scrollbar over, and nothing happens). How do you link the data to the scroll bar so that the scroll bar actually moves the script? Thank you.


Comment by: Jan Karel Pieterse (8-8-2013 21:10:17) deeplink to this comment

Hi Tucker,

The basis is to open a new instance of the class and assign the scrollbar to it's control object that is declared "WithEvents" inside that class. As the example workbook shows you.


Comment by: Joseph Powell (24-1-2014 16:42:16) deeplink to this comment

Is it possible to utilize combo boxes to do dependent drop down lists?


Comment by: Jan Karel Pieterse (24-1-2014 18:18:55) deeplink to this comment

Hi Joseph,

Yes of course. How many dependent lists would you have?


Comment by: John (8-4-2015 01:21:50) deeplink to this comment

I use Control features all the time but as many others I have found Active X to be more troublesome.

One area that I find that I need assistance on is a File Older background "pop-up" that I have seen done in Excel as far back as 2003. What I mean is that say in a Invoice workbook that has the main worksheet that    looks like a normal page without grid that has Label buttons on it that takes to Folder Page popup (same as if a photo that has an embedded macro in it). On these folder pages (do not confuse this as another worksheet) bring up such things as product prices, photos of the product, costs, fields for changing all including price markups, deletions of products, etc. Another on the main invoice page has a label Button for adding shipping address or billing addresses and thus allowing edits. Again the pop-up after clicking the Label Buttom brings this pop-up vanilla file folder overlay on the main invoice page. wondering you know how to perform is this a combination of marrying Excel with MS Access since the two work together.

Thanks.
John


Comment by: Bill Savage (23-4-2015 18:39:36) deeplink to this comment

Jan,
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) deeplink to this comment

The following was copied from http://www.rondebruin.nl/win/s8/win003.htm

Code goes here
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.


Comment by: PAT CHOR (10-11-2015 10:05:05) deeplink to this comment

I Have sum question for VBA Code
in USER Form Can use THis Code
    

Controls("Combobox" & I).clear

but in the work sheet can't use Above code

how to use code in the worksheet???

Thx....


Comment by: Jan Karel Pieterse (10-11-2015 17:23:52) deeplink to this comment

Hi Pat,

Depends. What type of combobox is it?


Comment by: Ronald van der Sanden (22-11-2017 15:06:40) deeplink to this comment

Hi
Recently I have downloaded a time sheet with auto hidden scroll bar. Cannot find this type in the defeloper tap. How to get this very usefool bar.
Thanks


Comment by: Alan Elston (24-12-2017 08:57:57) deeplink to this comment

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. https://imgur.com/AqHDBBH ) .


Comment by: Alan Elston (24-12-2017 08:59:16) deeplink to this comment

I can certainly confirm that ActiveX controls can be the cause of many troubles. Currently I am unable to get them to work at all when embedded in Excel 2003 Worksheets. ( Embed in a UserForm they appear fine ) . In 2007+ they appear currently OK in all situations and usage, at least by me: The internet is, however, full of reports of the problems caused by ActiveX controls, and there sudden non working has been the subject of many Forum and Blog discussions. It would appear that the problems are not fully solved, and I will have to spend some time through Forums, etc.. to investigate further my current problem. They are a very useful tool, but it is a shame they appear not to be reliable, and therefore possibly not a good choice to use in a professional environment.


Comment by: ganesh ram (13-5-2018 12:39:16) deeplink to this comment

how to toggle between excel sheet to vb userform ?


Comment by: Jan Karel Pieterse (14-5-2018 08:23:20) deeplink to this comment

Hi Ganesh,

Are you asking how to activate a userform which is already showing, but does not have the focus (because the user clicked on Excel for example)?


Comment by: Alan Elston (24-1-2019 16:40:39) deeplink to this comment

Hi
For over a year , on and off, I have wanted to use ActiveX embedded in a worksheet controls in Excel 2003. Almost always they did not work.
I sat down for a few days and have sorted the problem out on about a dozen machines with operating system of XP, Vista and Win 7. Most had Office 2003 and an higher version like Office 2007 or higher as well.
If you have similar issues then check this out:
http://www.eileenslounge.com/viewtopic.php?f=18&t=31600&p=245201#p245201
Alan


Comment by: Sascha (30-7-2019 01:53:00) deeplink to this comment

Hi
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
Sascha


Comment by: Jan Karel Pieterse (30-7-2019 11:32:00) deeplink to this comment

Hi Sascha,

I would consider using Data, Validation for this purpose. See:
https://www.contextures.com/xlDataVal08.html


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.




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