IDropdownLists

Defines the public contract for the DropdownLists class, exposing worksheet-based dropdown management built on one-column ListObjects. Supports adding, removing, updating, sorting, translating, and validating dropdown entries, plus hyperlink navigation and diagnostic logging.

Elements

Wksh #

Worksheet hosting the dropdown lists

Signature:

Public Property Get Wksh() As Worksheet: End Property

Properties exposing the dropdown worksheet and list metadata. Returns the worksheet that contains the one-column ListObjects.

Returns: Worksheet. The host worksheet.

Exists #

Check whether a dropdown exists

Signature:

Public Property Get Exists(ByVal listName As String) As Boolean: End Property

Searches the internal registry for the specified dropdown name.

Parameters:

  • listName: String. Dropdown name to search for.

Returns: Boolean. True when the dropdown is found.

Name #

Name of the dropdown object

Signature:

Public Property Get Name() As String: End Property

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

Returns: String. The worksheet name.

LabelRange #

Label cell above a dropdown ListObject

Signature:

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

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

Parameters:

  • listName: String. Dropdown name to look up.

Returns: Range. The label cell, or Nothing.

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. Optionally includes the header row.

Parameters:

  • listName: String. Dropdown name to read from.
  • includeHeaders: Optional Boolean. When True, includes the header row. Defaults to False.

Returns: BetterArray. The dropdown entries.

Length #

Total number of dropdowns on the worksheet

Signature:

Public Property Get Length() As Long: End Property

Returns the count of registered dropdown lists.

Returns: Long. The dropdown count.

AllDropdowns #

All registered dropdown names

Signature:

Public Property Get AllDropdowns() As BetterArray: End Property

Returns a BetterArray containing every dropdown name registered on the worksheet.

Returns: BetterArray. The dropdown name list.

Modify

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")

Methods that add, remove, update, sort, and translate dropdowns. Creates a new one-column ListObject populated with the supplied values. Optionally adds 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:

Public Sub Remove(ByVal listName As String): End Sub

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

Parameters:

  • listName: String. Dropdown name to remove.

ClearList #

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 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 and optionally removes duplicates.

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 in the worksheet

Signature:

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

Sorts the dropdown ListObject 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:

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

Applies the supplied TranslationObject to the data body of every registered dropdown. Skips translation when the current language matches and force is False.

Parameters:

  • trads: Object. A TranslationObject providing translations.
  • force: Optional Boolean. When True, translates even if the language matches. Defaults to False.
  • exclude: Optional String. Substring filter to skip matching dropdowns.
Operations

SetValidation #

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)

Validation and hyperlink operations using dropdown references. Sets data validation on the target Range using the specified dropdown as the validation source. The alertType controls the severity shown when the entered value is not in the list.

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:

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

HasCheckings #

Whether the dropdown object has logged diagnostic messages

Signature:

Public Property Get HasCheckings() As Boolean: End Property

Diagnostic logging access. Returns True when at least one diagnostic entry has been recorded.

Returns: Boolean. True when diagnostic entries exist.

CheckingValues #

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As Object: End Property

Returns the internal IChecking object containing all logged messages. Returns Nothing when no entries have been recorded.

Returns: Object. An IChecking instance, or Nothing.