Excel WS controls - no VBA

This module introduces worksheet (WS) on sheet controls:

Data validation - top layer in cell drop-down list
ComboBox - drawing layer drop-down list (Form Control item 2 (FC#2))
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.

Drop-down lines (max): 8. Cannot be increased
List is cell oriented (top layer) - adopts cell width
List is cell oriented (top layer) - adopts cell width
Maximum number of items: 32,767 (2^15-1)
Return the index number with the Excel MATCH function

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*

Scroll Bar lines are always visible - depends on ListBox height - set by user
List is on the drawing layer - not tied to cell dimensions
Maximum number of items: 32,767 (2^15-1)
Scroll bar icon is always visible
Selected item index number (base 1) is returned to the Cell link cell
F3 Paste Names is available in the Input range selection item
Use only Selection type: Single. Other options require VBA coding

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 WS control does not permit direct entry in the box.

Drop-down lines (scroll): range 0 to 30,000 - set by user
List is on the drawing layer - not tied to cell dimensions
Maximum number of items: 32,767 (2^15-1)
ComboBox displays only 1 item until clicked
Drop-down icon is always visible
Example includes a Rectangle Shape, and a TextBox
Selected item index number (base 1) is returned to the Cell link cell
Uses Input Range, and Cell link
F3 Paste Names is available in the Input range selection item

Text box

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

4. Option Buttons with Group Box

See the example in the Option buttons example WB.

Download the file: Excel file (xlsx) [369 KB] used in this example
Download (examples version): Excel file (xlsx) Option buttons example [329 KB] including use of WS Option Buttons with Group Box
Development platform: Excel 2016 (64 bit)
Revised: Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)]