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)

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.

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.