Excel onsheet controls - no VBA


This module introduces:

  1. Data validation - top layer in cell drop-down list
  2. ComboBox - drawing layer drop-down list (Form Control item 2 (FC#2))
  3. ListBox - drawing layer (FC#5)

Item 2 (FC#2) and item 3 (FC#5) are available on the Form Controls set in the Controls group of the Developer tab (figure 1).


ws form controls
Fig 1: Form controls - top row FC#1 to FC#6, second row (available) FC#7 to FC#9

Other available Form controls include (figure 1, top row) Button (FC#1), Check Box (FC#3), Spin Button (FC#4), Option Button (FC#6), (figure 1 second row) Group Box (FC#7), Label (FC#8), and Scroll Bar (FC#9).

Unavailable (disabled, as shown by the greyed out appearance) items Text Field (FC#10), Combo List - Edit (FC#11), and Combo Drop-Down - Edit (FC#12). Read more about Excel 5 Dialog sheets and controls .


1. Data validation


Available on: the Data tab - Data > Data Tools > Data Validation. Adds a DropButton to a cell, with Settings, Input Message, and Error Alert user options. Only Allow: List is described here.


data validation
Fig 2: Data validation - with Validation criteria: Allow - List

2. ListBox


Available on: the Developer tab - Developer > Controls > Insert > Form Controls > List Box (Form Control)


xlf-2013-power-view-tab
Fig 3: ListBox - with Input range, and Cell link

3. ComboBox


Available on: the Developer tab - Developer > Controls > Insert > Form Controls > Combo Box (Form Control). A ComboBox is a combination of a list box and a text box. The onsheet control does not permit direct entry in the box.


xlf-2013-power-view-tab
Fig 3: ComboBox - with Input range, and Cell link (the same as ListBox), plus Drop down lines item

Text box

Available on: the Insert tab - Insert > Text > Text Box.