DropdownLists

Manages the dropdown lists of one worksheet. Each list is a one-column ListObject and carries a workbook-level name. Add creates a list, Remove deletes it, ClearList empties it, Update rewrites its values, and Sort and Translate reorder and relabel the entries. SetValidation puts a list behind a range of cells, and AddHyperLink and AddReturnLink link the dropdown worksheet with the cells that use it.

THE REGISTRY

Column C of the worksheet holds the dropdown name and column D the name of its ListObject, both under a header on row 2. Every public member reads those two columns once as a block and answers from that copy for the rest of the call. The copy is dropped when the call ends, so each call reads the columns again.

Depends on: HiddenNames, BetterArray, Checking, increment by default the counter on the number of dropdowns by 1. Everytime a, dropdown is added to the current worksheet. The counter is saved somewhere the, first cell fo the worksheet. It starts at 1., dropdowns in the current dropdown worksheet., Where all the dropdowns should start, sometimes there could be a need, for header for the dropdown (like in custom dropdowns, so we shift one line), The registry that maps a dropdown name to its ListObject name. The dropdown, name is in column C, the ListObject name in column D. Both columns carry a, header on DROPDOWNSTARTROW., Field positions inside one row of the registry block held in memory, Row 1 of the registry block is the column header, so data starts on row 2, Counter start values: the workbook counter starts at 1, the worksheet one at 0, checking objects

Version: 1.0 (2026-02-09)

Factory

Create #

create

Create a new DropdownLists instance

Signature:

Public Function Create(ByVal outsh As Worksheet, Optional ByVal hprefix As String = "dropdown_") As DropdownLists

Initialises a DropdownLists object on the given worksheet. Each dropdown is stored as a one-column ListObject; the header prefix distinguishes multiple dropdown worksheets in the same workbook. On first use the worksheet is formatted (gridlines removed, registry columns created) and an initialisation flag is stored via HiddenNames.

Parameters:

  • outsh: Worksheet. The worksheet to host the dropdown ListObjects.
  • hprefix: Optional String. Prefix for ListObject header names. Defaults to "dropdown_".

Returns: DropdownLists. The new instance.

Throws:

  • ProjectError.ElementNotFound When outsh is Nothing.
Modify

Add #

add

Add a new dropdown to the worksheet

Signature:

Public Sub Add(ByVal listValues As BetterArray, ByVal listName As String, _
    Optional ByVal addLabel As Boolean = False, _
    Optional ByVal counterPrefix As String = vbNullString, _
    Optional ByVal tableStyle As String = "TableStyleLight13")

Creates a one-column ListObject populated with the supplied values, registers the name-to-ListObject mapping, and creates a workbook-level named reference. Optionally writes a visible label above the ListObject and applies a table style. Silently exits when the dropdown already exists. The ListObject name is built before the counters move, so it carries the old workbook number, while the label is built after and carries the new worksheet number.

Parameters:

  • listValues: BetterArray. String values to populate the ListObject.
  • listName: String. Name to assign to the dropdown.
  • addLabel: Optional Boolean. When True, writes a label above the ListObject. Defaults to False.
  • counterPrefix: Optional String. Prefix for the label text. Defaults to vbNullString.
  • tableStyle: Optional String. Excel table style name. Defaults to "TableStyleLight13".

Remove #

remove

Remove a dropdown from the worksheet

Signature:

Public Sub Remove(ByVal listName As String)

Deletes the ListObject, its workbook-level name reference, the internal registry entry, and decrements both counters. Silently exits when the dropdown does not exist.

Parameters:

  • listName: String. Dropdown name to remove.

ClearList #

clear-list

Clear entries of a dropdown

Signature:

Public Sub ClearList(ByVal listName As String)

Removes all data rows from the dropdown ListObject without deleting the dropdown itself. Silently exits when the dropdown does not exist.

Parameters:

  • listName: String. Dropdown name to clear.

Update #

update

Update a dropdown with new values

Signature:

Public Sub Update(ByVal UpdateData As BetterArray, ByVal listName As String, _
    Optional ByVal pasteAtBottom As Boolean = False, _
    Optional ByVal removeDuplicates As Boolean = True)

Replaces or appends values in the specified dropdown. When pasteAtBottom is True, appends below existing data. Duplicate entries are removed when removeDuplicates is True. Silently exits when the dropdown does not exist or the data is empty.

Parameters:

  • UpdateData: BetterArray. New values to write.
  • listName: String. Dropdown name to update.
  • pasteAtBottom: Optional Boolean. When True, appends below existing rows. Defaults to False.
  • removeDuplicates: Optional Boolean. When True, removes duplicate entries. Defaults to True.

Sort #

sort

Sort a dropdown ListObject

Signature:

Public Sub Sort(ByVal listName As String, _
    Optional ByVal sortOrder As Long = xlAscending)

Sorts the dropdown entries using the specified sort order. Silently exits when the dropdown does not exist.

Parameters:

  • listName: String. Dropdown name to sort.
  • sortOrder: Optional Long. xlSortOrder enumeration value. Defaults to xlAscending.

Translate #

translate

Translate all dropdowns on the worksheet

Signature:

Public Sub Translate(ByVal trads As Object, Optional ByVal force As Boolean = False, Optional ByVal exclude As String)

Iterates over every registered ListObject and applies the supplied TranslationObject to its data body. Skips translation when the current language matches and force is False. ListObjects whose name contains the exclude substring are skipped.

Parameters:

  • trads: Object. A TranslationObject providing translations.
  • force: Optional Boolean. When True, translates regardless of language match. Defaults to False.
  • exclude: Optional String. Substring filter to skip matching ListObjects.
Operations

SetValidation #

set-validation

Apply dropdown validation to a Range

Signature:

Public Sub SetValidation(ByVal cellRng As Range, ByVal listName As String, _
    Optional ByVal alertType As String = "info", _
    Optional ByVal message As String = vbNullString, _
    Optional ByVal showError As Boolean = True)

Sets data validation on the target Range using the specified dropdown as the validation source. alertType controls the severity: "info" shows an information alert, "warning" shows a warning, and "error" prevents entry of values outside the list. Silently exits when the dropdown or range does not exist.

Parameters:

  • cellRng: Range. Target range to validate.
  • listName: String. Dropdown name to use as the validation source.
  • alertType: Optional String. Alert severity ("info", "warning", "error"). Defaults to "info".
  • message: Optional String. Message shown on validation failure. Defaults to vbNullString.
  • showError: Optional Boolean. When True, displays validation feedback. Defaults to True.

add-return-link

Add a return hyperlink on the dropdown header

Signature:

Public Sub AddReturnLink(ByVal listName As String, ByVal returnRng As Range)

Adds a hyperlink on the dropdown label cell that navigates back to the specified range.

Parameters:

  • listName: String. Dropdown whose label receives the link.
  • returnRng: Range. Target destination of the hyperlink.

add-hyperlink

Add a hyperlink on a cell targeting a dropdown

Signature:

Public Sub AddHyperLink(ByVal listName As String, ByVal linkRng As Range)

Adds a hyperlink on the specified range that navigates to the label cell of the dropdown.

Parameters:

  • listName: String. Dropdown to navigate to.
  • linkRng: Range. Cell that receives the hyperlink.
Internal members (not exported)

Elements

Wksh #

wksh

Worksheet hosting the dropdown lists

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The host worksheet.

Wksh #

wksh-set

Assign the worksheet and initialise the dropdown registry

Signature:

Public Property Set Wksh(ByVal outsh As Worksheet)

On first assignment, formats the worksheet (removes gridlines, creates internal registry columns) and stores an initialisation flag. Resets cached HiddenNames stores on every assignment.

Parameters:

AllDropdowns #

all-dropdowns

All registered dropdown names

Signature:

Public Property Get AllDropdowns() As BetterArray

Scans the internal registry column and collects every dropdown name into a BetterArray.

Returns: BetterArray. The dropdown name list.

headerPrefix #

header-prefix

Header prefix used for ListObject names

Signature:

Public Property Get headerPrefix() As String

Returns: String. The current prefix.

headerPrefix #

header-prefix-set

Assign the header prefix

Signature:

Public Property Let headerPrefix(ByVal hprefix As String)

Parameters:

Name #

name

Name of the dropdown object

Signature:

Public Property Get Name() As String

Returns the name of the worksheet that hosts the dropdown lists.

Returns: String. The worksheet name.

Registry

RefreshRegistry #

refresh-registry

Read the dropdown registry again from the worksheet

Signature:

Private Sub RefreshRegistry()

A routine that changes the registry calls this once at its start. One call reads the two registry columns a single time, and every lookup after that runs in memory. The copy is dropped first because another DropdownLists object can hold the same worksheet and change the registry in between.

SetValidation and AddLink read the registry and never write it, so they call EnsureRegistry and keep the copy. One SetupPreparation.Prepare makes 56 SetValidation calls, and each forced reload costs two worksheet crossings for bookkeeping alone.

EnsureRegistry #

ensure-registry

Load the registry when no copy is held

Signature:

Private Sub EnsureRegistry()

LoadRegistry #

load-registry

Read the two registry columns into memory in one block

Signature:

Private Sub LoadRegistry()

Reads from the header row down to the last used row of the dropdown name column. Row 1 of the block is the header, so dropdown entries start at REGISTRYFIRSTDATAITEM.

RegistryItem #

registry-item

Read one field of one registry row as text

Signature:

Private Function RegistryItem(ByVal rowIndex As Long, ByVal fieldIndex As Long) As String

Parameters:

Returns: String. The stored text, or an empty string when out of range.

RegistryIndexOf #

registry-index-of

Find the registry row that holds a dropdown name

Signature:

Private Function RegistryIndexOf(ByVal listName As String) As Long

Parameters:

Returns: Long. Row position inside the registry block, or 0 when absent.

RegistryRow #

registry-row

Worksheet row of a registry block position

Signature:

Private Function RegistryRow(ByVal rowIndex As Long) As Long

Parameters:

Returns: Long. The 1-based worksheet row.

RegistryFreeRow #

registry-free-row

First worksheet row free for a new registry entry

Signature:

Private Function RegistryFreeRow() As Long

A removed dropdown leaves an empty row behind, and that row is used again before the registry grows.

Returns: Long. The 1-based worksheet row to write on.

HasDropdown #

has-dropdown

Whether a dropdown name is in the registry held in memory

Signature:

Private Function HasDropdown(ByVal listName As String) As Boolean

Parameters:

Returns: Boolean. True when the name is registered.

MissingDropdown #

missing-dropdown

Report a dropdown that is not on the worksheet

Signature:

Private Function MissingDropdown(ByVal listName As String, ByVal message As String, _
    Optional ByVal showTrace As Boolean = True) As Boolean

Logs the given message and prints the debug trace when the dropdown is absent. Callers use the return value to leave at once.

Parameters:

Returns: Boolean. True when the dropdown does not exist.

Counters

InternalStore #

internal-store

Lazy-loaded HiddenNames store for workbook or sheet scope

Signature:

Private Property Get InternalStore(Optional ByVal sheetLevel As Boolean = False) As HiddenNames

Returns the cached HiddenNames instance for either the workbook (default) or the worksheet. Creates the store on first access.

Parameters:

Returns: HiddenNames. The requested store.

CounterName #

counter-name

Stored name of a dropdown counter

Signature:

Private Function CounterName(ByVal sheetLevel As Boolean) As String

Parameters:

Returns: String. The hidden name holding the counter.

CounterStart #

counter-start

Start value of a dropdown counter

Signature:

Private Function CounterStart(ByVal sheetLevel As Boolean) As Long

Parameters:

Returns: Long. The value the counter takes when it is created.

CounterStore #

counter-store

Store holding a dropdown counter, created when missing

Signature:

Private Function CounterStore(ByVal sheetLevel As Boolean) As HiddenNames

The counters are shared by every DropdownLists object of the workbook, so they live in HiddenNames and never in memory. Only the store object is cached.

Parameters:

Returns: HiddenNames. The store that holds the counter.

CounterValue #

counter-value

Read a dropdown counter

Signature:

Private Function CounterValue(ByVal sheetLevel As Boolean) As Long

Parameters:

Returns: Long. The counter value.

StepCounter #

step-counter

Move one dropdown counter and write it through at once

Signature:

Private Sub StepCounter(ByVal sheetLevel As Boolean, ByVal stepValue As Long)

The worksheet counter never goes below zero. The value is written back to the store on every step because other DropdownLists objects read the same counter.

Parameters:

StepCounters #

step-counters

Move the worksheet and workbook counters together

Signature:

Private Sub StepCounters(ByVal stepValue As Long)

Parameters:

IncrementCounters #

increment-counters

Increment both workbook and worksheet counters

Signature:

Private Sub IncrementCounters()

DecrementCounters #

decrement-counters

Decrement both workbook and worksheet counters

Signature:

Private Sub DecrementCounters()

Helpers

RemoveGridLines #

remove-gridlines

Remove gridlines from a worksheet view

Signature:

Private Sub RemoveGridLines(ByVal sh As Worksheet, Optional DisplayZeros As Boolean = False)

Parameters:

LastColumn #

last-column

Find the next available column for a new dropdown

Signature:

Private Function LastColumn() As Long

Returns the column index two positions to the right of the rightmost occupied header cell. This leaves a blank separator column between adjacent ListObjects.

Returns: Long. The 1-based column index.

SanitizedListName #

sanitized-list-name

Sanitise a dropdown name for use in ListObject naming

Signature:

Private Function SanitizedListName(ByVal listName As String) As String

Parameters:

Returns: String. The trimmed and space-replaced name.

ListObjectHeaderName #

list-object-header-name

Build the ListObject header name for a dropdown

Signature:

Private Function ListObjectHeaderName(ByVal listName As String) As String

Parameters:

Returns: String. The prefixed header name.

CorrespondingWorkbookName #

corresponding-workbook-name

Workbook-level name used for the dropdown reference

Signature:

Private Property Get CorrespondingWorkbookName(ByVal listName As String) As String

Combines the header prefix with the ListObject header name. When no prefix is set, falls back to the DEFAULTNAMEPREFIX constant.

Parameters:

Returns: String. The workbook-level reference name.

ListObjectName #

list-object-name

ListObject name for a dropdown

Signature:

Private Property Get ListObjectName(ByVal listName As String) As String

Returns the stored ListObject name for an existing dropdown, or generates and registers a new one for a dropdown being created. The workbook counter is what keeps the generated name unique across the whole workbook, so two worksheets can hold a dropdown of the same name.

Parameters:

Returns: String. The ListObject name.

DropdownListObject #

dropdown-list-object

ListObject that holds the values of a dropdown

Signature:

Private Function DropdownListObject(ByVal listName As String) As ListObject

Parameters:

Returns: ListObject. The table on the dropdown worksheet.

DropdownLabelCell #

dropdown-label-cell

Cell that carries the label of a dropdown

Signature:

Private Function DropdownLabelCell(ByVal listName As String) As Range

The label sits one row above the header row of the ListObject.

Parameters:

Returns: Range. The label cell.

FormatLabelCell #

format-label-cell

Write and format the label cell of a dropdown

Signature:

Private Sub FormatLabelCell(ByVal labelCell As Range, ByVal labelText As String, _
    ByVal interiorColor As Long, ByVal fontColor As Long)

Used when a dropdown is added, and again after a hyperlink is placed on the label, because adding a hyperlink wipes the text and the format.

Parameters:

AddLoName #

add-lo-name

Register a dropdown-to-ListObject name mapping

Signature:

Private Sub AddLoName(ByVal listName As String, ByVal loName As String)

Parameters:

RemoveLoName #

remove-lo-name

Remove a dropdown-to-ListObject name mapping

Signature:

Private Sub RemoveLoName(ByVal lst As String)

Parameters:

Length #

length

Total number of dropdowns on the worksheet

Signature:

Public Property Get Length() As Long

Returns: Long. The dropdown count.

CurrentDropdownLabel #

current-dropdown-label

Build the display label for the next dropdown

Signature:

Private Function CurrentDropdownLabel(ByVal labelPrefix As String) As String

Combines the prefix with the current counter to produce a human-readable label (e.g. "Custom Dropdown 3").

Parameters:

Returns: String. The assembled label.

Exists #

exists

Check whether a dropdown exists on the worksheet

Signature:

Public Function Exists(ByVal listName As String) As Boolean

Looks only at the registry of this worksheet, so two worksheets can hold a dropdown of the same name without seeing each other.

Parameters:

Returns: Boolean. True when the dropdown is found.

WorkbookName #

workbook-name

Workbook-level name a dropdown answers to

Signature:

Public Property Get WorkbookName(ByVal listName As String) As String

The name SetValidation points a cell at, and the name any other reader uses to reach the values. It is derived from the list name and the header prefix, so it can be asked for before the dropdown is added.

This is here because a reader outside the class needs the name in a place where building a manager is too costly - a worksheet function called once per formula cell reads the range by this name and nothing else.

Parameters:

Returns: String. The workbook-level name.

LabelRange #

label-range

Label cell above a dropdown ListObject

Signature:

Public Property Get LabelRange(ByVal listName As String) As Range

Returns the cell directly above the header row of the specified dropdown ListObject. Returns Nothing when the dropdown does not exist.

Parameters:

Returns: Range. The label cell, or Nothing.

Values #

values

Retrieve the values of a dropdown

Signature:

Public Property Get Values(ByVal listName As String, Optional ByVal includeHeaders As Boolean = False) As BetterArray

Returns a BetterArray of the entries stored in the specified dropdown. Logs a diagnostic note and exits when the dropdown does not exist.

Parameters:

Returns: BetterArray. The dropdown entries.

Operations

add-link

Add a hyperlink between a dropdown and a cell

Signature:

Private Sub AddLink(ByVal listName As String, ByVal targetRng As Range, _
    Optional ByVal returnLink As Boolean = False)

Creates a hyperlink that navigates between the dropdown label cell and the specified target range. When returnLink is True, the link is placed on the dropdown label pointing at targetRng; when False, the link is placed on targetRng pointing at the dropdown label. Preserves the original cell formatting after link insertion.

Parameters:

Checkings

LogInfo #

log-info

Record a diagnostic entry

Signature:

Private Sub LogInfo(ByVal label As String, _
    Optional ByVal scope As Byte = checkingNote)

Parameters:

HasCheckings #

has-checkings

Whether the dropdown object has logged diagnostic messages

Signature:

Public Property Get HasCheckings() As Boolean

Returns: Boolean. True when diagnostic entries exist.

CheckingValues #

checking-values

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As Object

Returns: Object. An Checking instance, or Nothing.

ShowDebug #

show-debug

Print a debug trace to the Immediate window

Signature:

Private Sub ShowDebug()

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

Private Sub ThrowError(ByVal errNumb As Long, ByVal errorMessage As String)

Parameters:

Throws:

Seal #

seal

Seal the instance so setup setters can no longer be written

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Guard a setup setter against writes after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Used in (50 file(s))