DropdownLists

Manages worksheet-based dropdown lists backed by one-column ListObjects. Each dropdown is registered with an internal name-to-ListObject mapping and a workbook-level named reference. Supports adding, removing, updating, sorting, translating, and validating dropdown entries, plus hyperlink navigation between the dropdown sheet and consuming cells.

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), checking objects

Version: 1.0 (2026-02-09)

Factory

create #

Create a new DropdownLists instance

Signature:

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

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: IDropdownLists. The new instance.

Throws:

  • ProjectError.ElementNotFound When outsh is Nothing.
Modify

add #

Add a new dropdown to the worksheet

Signature:

Private 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.

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 a dropdown from the worksheet

Signature:

Private 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.

clear-list #

Clear entries of a dropdown

Signature:

Private 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 a dropdown with new values

Signature:

Private 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 a dropdown ListObject

Signature:

Private 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 all dropdowns on the worksheet

Signature:

Private 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

set-validation #

Apply dropdown validation to a Range

Signature:

Private 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 a return hyperlink on the dropdown header

Signature:

Private 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 a hyperlink on a cell targeting a dropdown

Signature:

Private 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

self #

Self-reference returning the interface pointer

Signature:

Public Property Get Self() As IDropdownLists

Returns: IDropdownLists. This instance.

wksh #

Worksheet hosting the dropdown lists

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The host worksheet.

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:

all-dropdowns #

All registered dropdown names

Signature:

Private Property Get AllDropdowns() As BetterArray

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

Returns: BetterArray. The dropdown name list.

header-prefix #

Header prefix used for ListObject names

Signature:

Public Property Get headerPrefix() As String

Returns: String. The current prefix.

header-prefix-set #

Assign the header prefix

Signature:

Public Property Let headerPrefix(ByVal hprefix As String)

Parameters:

name #

Name of the dropdown object

Signature:

Private Property Get Name() As String

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

Returns: String. The worksheet name.

Workbook Counter

internal-store #

Lazy-loaded HiddenNames store for workbook or sheet scope

Signature:

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

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

Parameters:

Returns: IHiddenNames. The requested store.

ensure-workbook-counter #

Ensure the workbook-level dropdown counter exists

Signature:

Private Sub EnsureWorkbookCounter()

workbook-counter-value #

Read the current workbook dropdown counter

Signature:

Private Function WorkbookCounterValue() As Long

Returns: Long. The counter value.

set-workbook-counter #

Write a new workbook dropdown counter value

Signature:

Private Sub SetWorkbookCounter(ByVal newValue As Long)

Parameters:

increment-workbook-counter #

Increment the workbook dropdown counter by one

Signature:

Private Sub IncrementWorkbookCounter()

decrement-workbook-counter #

Decrement the workbook dropdown counter by one

Signature:

Private Sub DecrementWorkbookCounter()

Worksheet Counter

ensure-worksheet-counter #

Ensure the worksheet-level dropdown counter exists

Signature:

Private Sub EnsureWorksheetCounter()

sheet-counter-value #

Read the current worksheet dropdown counter

Signature:

Private Function SheetCounterValue() As Long

Returns: Long. The counter value.

set-sheet-counter #

Write a new worksheet dropdown counter value

Signature:

Private Sub SetSheetCounter(ByVal newValue As Long)

Parameters:

increment-sheet-counter #

Increment the worksheet dropdown counter by one

Signature:

Private Sub IncrementSheetCounter()

decrement-sheet-counter #

Decrement the worksheet dropdown counter by one

Signature:

Private Sub DecrementSheetCounter()

increment-counters #

Increment both workbook and worksheet counters

Signature:

Private Sub IncrementCounters()

decrement-counters #

Decrement both workbook and worksheet counters

Signature:

Private Sub DecrementCounters()

Helpers

remove-gridlines #

Remove gridlines from a worksheet view

Signature:

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

Parameters:

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.

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.

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.

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.

list-object-name #

ListObject name for a dropdown

Signature:

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

Returns the cached ListObject name for an existing dropdown, or generates and registers a new one for a dropdown being created.

Parameters:

Returns: String. The ListObject name.

add-lo-name #

Register a dropdown-to-ListObject name mapping

Signature:

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

Parameters:

retrieve-lo-name #

Retrieve the ListObject name for a dropdown

Signature:

Private Property Get RetrieveLoName(ByVal lst As String) As String

Parameters:

Returns: String. The ListObject name, or vbNullString when not found.

remove-lo-name #

Remove a dropdown-to-ListObject name mapping

Signature:

Private Sub RemoveLoName(ByVal lst As String)

Parameters:

length #

Total number of dropdowns on the worksheet

Signature:

Private Property Get Length() As Long

Returns: Long. The dropdown count.

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 #

Check whether a dropdown exists on the worksheet

Signature:

Private Function Exists(ByVal listName As String) As Boolean

Parameters:

Returns: Boolean. True when the dropdown is found.

label-range #

Label cell above a dropdown ListObject

Signature:

Private 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 #

Retrieve the values of a dropdown

Signature:

Private 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 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

log-info #

Record a diagnostic entry

Signature:

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

Parameters:

has-checkings #

Whether the dropdown object has logged diagnostic messages

Signature:

Private Property Get HasCheckings() As Boolean

Returns: Boolean. True when diagnostic entries exist.

checking-values #

Retrieve the diagnostic log entries

Signature:

Private Property Get CheckingValues() As Object

Returns: Object. An IChecking instance, or Nothing.

show-debug #

Print a debug trace to the Immediate window

Signature:

Private Sub ShowDebug()

throw-error #

Raise a ProjectError-based exception

Signature:

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

Parameters:

Throws:

Used in (52 file(s))