Passwords

Manages workbook/worksheet protection, key generation, debug-mode toggling, and import/export of password metadata. Uses TypeName dispatch to handle Workbook, Worksheet, and String target arguments through a single Protect/UnProtect entry point.

Depends on: Checking, BetterArray, ProjectError, ITranslationObject

Version: 1.0 (2026-02-09)

Core Accessors

create #

Create an instance of password

Signature:

Public Function Create(ByVal sh As Worksheet) As IPasswords

Factory returning an interface reference that wraps the provided password worksheet.

Parameters:

sh: Worksheet. Worksheet hosting password tables and named ranges.

Returns: IPasswords. Fully initialised password handler.

Debug Mode

enter-debug-mode #

Put the workbook into a fully unprotected debug state

Signature:

Public Sub EnterDebugMode(Optional ByVal targetWorkbook As Workbook)

Parameters:

targetWorkbook: Optional Workbook. Target workbook. Defaults to the bound workbook.

leave-debug-mode #

Restore protections after debug mode

Signature:

Public Sub LeaveDebugMode(Optional ByVal targetWorkbook As Workbook)

Parameters:

targetWorkbook: Optional Workbook. Target workbook. Defaults to the bound workbook.

ensure-protected-sheets-name #

Ensure the workbook exposes a named range referencing the protection table

Signature:

Public Sub EnsureProtectedSheetsName(Optional ByVal targetWorkbook As Workbook)

Parameters:

targetWorkbook: Optional Workbook. Target workbook. Defaults to the bound workbook.

ensure-debug-exit-handler #

Inject before-close procedures that leave debug mode automatically

Signature:

Public Sub EnsureDebugExitHandler(Optional ByVal targetWorkbook As Workbook)

Parameters:

targetWorkbook: Optional Workbook. Target workbook. Defaults to the bound workbook.

Protection

protect #

Protect a worksheet or workbook

Signature:

Private Sub Protect(Optional  targetObject As Variant = "_active", _
                   Optional ByVal allowShapes As Boolean = True, _
                   Optional ByVal allowDeletingRows As Boolean = True, _
                   Optional ByVal registerState As Boolean = True)

Parameters:

targetObject: Optional Variant. Workbook, Worksheet, or String tag. Defaults to "_active".
allowShapes: Optional Boolean. Allow shape editing. Defaults to True.
allowDeletingRows: Optional Boolean. Allow row deletion. Defaults to True.
registerState: Optional Boolean. Persist protection metadata. Defaults to True.

unprotect #

Unprotect a worksheet or workbook

Signature:

Private Sub UnProtect(Optional targetObject As Variant = "_active")

Parameters:

targetObject: Optional Variant. Workbook, Worksheet, or String tag. Defaults to "_active".

Keys

generate-key #

Randomly select a public/private key pair

Signature:

Private Sub GenerateKey(ByVal trads As ITranslationObject)

Parameters:

trads: ITranslationObject. Translation helper for prompts.

display-private-key #

Display the current private key via a translated prompt

Signature:

Private Sub DisplayPrivateKey(ByVal trads As ITranslationObject)

Parameters:

trads: ITranslationObject. Translation helper for the message.

DataExchange

export-to-workbook #

Export the password worksheet into another workbook

Signature:

Private Sub ExportToWorkbook(ByVal destination As Workbook)

Parameters:

destination: Workbook. Target workbook.

import-from #

Import keys and metadata from another password handler

Signature:

Private Sub ImportFrom(ByVal source As IPasswords)

Parameters:

source: IPasswords. Source password object.

new-debuging-password #

Update the debugging password stored on the password worksheet

Signature:

Private Sub UpdateDebugingPassword(ByVal newValue As String)

Parameters:

newValue: String. The new debugging password value.

clone-to-worksheet #

Clone the password worksheet into another sheet

Signature:

Private Function CloneToWorksheet(ByVal targetSheet As Worksheet) As IPasswords

Parameters:

targetSheet: Worksheet. Destination worksheet.

Returns: IPasswords. Handler bound to the cloned sheet.

clone-to-workbook #

Clone the password worksheet into another workbook

Signature:

Private Function CloneToWorkbook(ByVal wb As Workbook) As IPasswords

Exports the password data to the target workbook and returns a new handler bound to the cloned sheet.

Parameters:

wb: Workbook. Destination workbook.

Returns: IPasswords. Handler bound to the cloned sheet.

Private Helpers

apply-protection-matrix #

Apply stored protection settings to all listed worksheets

Signature:

Private Sub ApplyProtectionMatrix(ByVal wb As Workbook)

Parameters:

wb: Workbook. Target workbook whose sheets will be protected.

Internal members (not exported)

Core Accessors

self #

Self-reference as IPasswords

Signature:

Public Property Get Self() As IPasswords

Returns: IPasswords. Self-reference.

password-sheet-set #

Assign the password worksheet and reset caches

Signature:

Public Property Set PasswordSheet(ByVal sh As Worksheet)

Parameters:

sh: Worksheet. The password worksheet to bind.

name #

Name of the password worksheet

Signature:

Public Property Get Name() As String

Returns: String. The worksheet name.

password-sheet #

Worksheet hosting the password tables

Signature:

Public Property Get PasswordSheet() As Worksheet

Returns: Worksheet. The password worksheet.

password-workbook #

Workbook owning the password worksheet

Signature:

Public Property Get PasswordWorkbook() As Workbook

Returns: Workbook. The parent workbook.

display-prompts #

Whether UI prompts are displayed during operations

Signature:

Public Property Get DisplayPrompts() As Boolean

Returns: Boolean. True when prompts are enabled.

display-prompts-set #

Toggle UI prompt display

Signature:

Public Property Let DisplayPrompts(ByVal value As Boolean)

last-private-prompt #

Most recent private key prompt text

Signature:

Public Property Get LastPrivatePrompt() As String

Returns: String. The prompt text.

last-private-prompt-title #

Most recent private key prompt title

Signature:

Public Property Get LastPrivatePromptTitle() As String

Returns: String. The prompt title.

value #

Retrieve a range value

Signature:

Public Property Get Value(ByVal tagName As String) As String

Retrieve the value stored in the named range associated with the supplied tag.

Parameters:

tagName: A tag identifier (debuggingpassword, publickey, privatekey, version, labpublickey, labprivatekey).

Returns: A String, value stored in the named range associated with the tag.

tag-value #

Retrieve a value from a byte-coded tag

Signature:

Private Property Get TagValue(ByVal tagId As Byte) As String

Parameters:

tagId: Byte. PasswordTag constant.

Returns: String. The stored value.

table-range #

Retrieve the range of a password-managed table

Signature:

Public Property Get TableRange(Optional ByVal tableName As String = TABLEKEYS, _
                               Optional ByVal includeHeaders As Boolean = False) As Range

Looks up the specified ListObject on the password worksheet and returns either the full range or data-only range.

Parameters:

tableName: String. Table identifier (T_keys or T_ProtectedSheets). Defaults to TABLEKEYS.
includeHeaders: Boolean. When True, includes the header row. Defaults to False.

Returns: Range. The requested table range.

has-checkings #

Whether any diagnostic entries have been captured

Signature:

Public Property Get HasCheckings() As Boolean

Returns: Boolean. True when at least one entry exists.

checking-values #

Retrieve the aggregated checking entries

Signature:

Public Property Get CheckingValues() As IChecking

Returns: IChecking. The checking object, or Nothing.

Protection

protect-sheet #

Protect a worksheet with the debugging password

Signature:

Private Sub ProtectSheet(sheetObject As Variant, _
                        Optional ByVal allowShapes As Boolean = True, _
                        Optional ByVal allowDeletingRows As Boolean = True, _
                        Optional ByVal registerState As Boolean = True)

Parameters:

sheetObject: Variant. Worksheet or sheet name.
allowShapes: Boolean. Permit shape manipulation. Defaults to True.
allowDeletingRows: Boolean. Enable row deletion. Defaults to True.
registerState: Boolean. Reuse stored table settings. Defaults to True.

unprotect-sheet #

Unprotect a worksheet using the stored debugging password

Signature:

Private Sub UnprotectSheet(Optional sheetObject As Variant)

Parameters:

sheetObject: Optional Variant. Worksheet reference or sheet name. Defaults to the active sheet.

resolve-worksheet-protection #

Dispatch worksheet protect or unprotect

Signature:

Private Sub ResolveWorksheetProtection( sheetObject As Variant, _
                        Optional ByVal allowShapes As Boolean = True, _
                        Optional ByVal allowDeletingRows As Boolean = True, _
                        Optional ByVal registerState As Boolean = True, _
                        Optional ByVal setProtection As Boolean = True)

Parameters:

sheetObject: Variant. Worksheet reference or sheet name.
allowShapes: Optional Boolean. Allow shape editing when protected. Defaults to True.
allowDeletingRows: Optional Boolean. Allow row deletion when protected. Defaults to True.
registerState: Optional Boolean. Persist protection metadata. Defaults to True.
setProtection: Optional Boolean. When True, protects; when False, unprotects. Defaults to True.

protect-workbook #

Protect workbook structure with the debugging password

Signature:

Private Sub ProtectWorkbook(targetObject As Variant)

Parameters:

targetObject: Variant. Workbook reference.

unprotect-workbook #

Unprotect workbook structure using the debugging password

Signature:

Private Sub UnprotectWorkbook(targetObject As Variant)

Parameters:

targetObject: Variant. Workbook reference.

resolve-workbook-protection #

Dispatch workbook protect or unprotect

Signature:

Private Sub ResolveWorkbookProtection(targetObject As Variant, Optional ByVal setProtection As Boolean = True)

Parameters:

targetObject: Variant. Workbook reference.
setProtection: Optional Boolean. When True, protects; when False, unprotects. Defaults to True.

resolve-protection #

Dispatch protect/unprotect via TypeName

Signature:

Private Sub ResolveProtection(Optional  targetObject As Variant = "_active", _
                   Optional ByVal allowShapes As Boolean = True, _
                   Optional ByVal allowDeletingRows As Boolean = True, _
                   Optional ByVal registerState As Boolean = True, _
                   Optional ByVal setProtection As Boolean = True)

Accepts a Workbook, Worksheet, or String. The string "_active" targets the active worksheet; "_wbactive" targets the active workbook.

Parameters:

targetObject: Optional Variant. Workbook, Worksheet, or String tag. Defaults to "_active".
allowShapes: Optional Boolean. Allow shape editing when protected. Defaults to True.
allowDeletingRows: Optional Boolean. Allow row deletion when protected. Defaults to True.
registerState: Optional Boolean. Persist protection metadata. Defaults to True.
setProtection: Optional Boolean. When True, protects; when False, unprotects. Defaults to True.

Private Helpers

validate-sheet #

Validate the password worksheet

Signature:

Private Sub ValidateSheet(ByVal sh As Worksheet)

ensure-sheet-ready #

Ensure password worksheet is initialised

Signature:

Private Sub EnsureSheetReady()

ensure-workbook-ready #

Ensure the password workbook reference is resolved

Signature:

Private Sub EnsureWorkbookReady()

ensure-lo-exists #

Validate that a ListObject exists on a worksheet

Signature:

Private Sub EnsureLoExists(ByVal sh As Worksheet, ByVal loName As String)

reset-caches #

Clear cached ListObject references

Signature:

Private Sub ResetCaches()

keys-table #

Retrieve and cache the keys ListObject

Signature:

Private Function KeysTable() As ListObject

Returns: ListObject. The keys table.

protected-sheets-table #

Retrieve and cache the protected-sheets ListObject

Signature:

Private Function ProtectedSheetsTable() As ListObject

Returns: ListObject. The protection table.

named-range #

Retrieve a named range from the password worksheet

Signature:

Private Function NamedRange(ByVal rangeName As String) As Range

Returns: Range. The named range.

resolve-workbook #

Resolve a workbook from optional candidate

Signature:

Private Function ResolveWorkbook(Optional candidate As Variant) As Workbook

Returns: Workbook. The resolved workbook.

resolve-sheet #

Resolve a worksheet from a Variant argument

Signature:

Private Function ResolveSheet(sheetObject As Variant) As Worksheet

Returns: Worksheet. The resolved worksheet.

tag-to-range-name #

Map a tag identifier to its named range name

Signature:

Private Function TagToRangeName(ByVal tagId As Byte) As String

Returns: String. The named range identifier.

tag-from-name #

Convert a friendly tag label to a byte-coded identifier

Signature:

Private Function TagFromName(ByVal tagName As String) As Byte

Returns: Byte. The PasswordTag constant.

ensure-range-exists #

Validate that a named range exists on a worksheet

Signature:

Private Sub EnsureRangeExists(ByVal targetSheet As Worksheet, ByVal rangeName As String)

is-in-debug-mode #

Test whether the workbook is in debug mode

Signature:

Private Function InDebugMode() As Boolean

Returns: Boolean. True when debug mode is active.

apply-sheet-protection #

Protect a worksheet using debugging password and capability flags

Signature:

Private Sub ApplySheetProtection(ByVal targetSheet As Worksheet, _
                                 ByVal allowShapes As Boolean, _
                                 ByVal allowDeleting As Boolean, _
                                 ByVal pwd As String)

protection-record #

Retrieve stored protection preferences for a sheet

Signature:

Private Function ProtectionRecord(ByVal sheetName As String) As BetterArray

Returns: BetterArray. Array(sheetName, allowShapes, allowDelete).

empty-protection-record #

Construct default protection settings for a sheet

Signature:

Private Function EmptyProtectionRecord(ByVal sheetName As String) As BetterArray

Returns: BetterArray. Array(sheetName, "yes", "yes").

persist-protection-settings #

Insert or update the protection table entry for a sheet

Signature:

Private Sub PersistProtectionSettings(ByVal sheetName As String, _
                                      ByVal allowShapes As Boolean, _
                                      ByVal allowDeletingRows As Boolean)

boolean-to-flag #

Convert a Boolean to "yes"/"no" text flag

Signature:

Private Function BooleanToFlag(ByVal inpValue As Boolean) As String

Returns: String. "yes" or "no".

parse-boolean #

Parse a "yes"/"no" text flag to Boolean

Signature:

Private Function ParseBoolean(ByVal inpValue As String) As Boolean

Returns: Boolean. True unless the value is "no".

build-leave-debug-procedure #

Generate VBA code for the LeaveDebugModeOnClose procedure

Signature:

Private Function BuildLeaveDebugProcedure() As String

Returns: String. VBA source code.

build-before-close-procedure #

Generate VBA code for the Workbook_BeforeClose handler

Signature:

Private Function BuildBeforeCloseProcedure() As String

Returns: String. VBA source code.

ensure-workbook-before-close-hook #

Ensure Workbook_BeforeClose invokes the debug guard

Signature:

Private Sub EnsureWorkbookBeforeCloseHook(ByVal component As Object)

procedure-block-contains-token #

Scan a procedure block for a specific token

Signature:

Private Function ProcedureBlockContainsToken(ByVal codeModule As Object, _
                                             ByVal startLine As Long, _
                                             ByVal lineCount As Long, _
                                             ByVal token As String) As Boolean

Returns: Boolean. True when the token is found.

append-procedure #

Append generated code to the end of a code module

Signature:

Private Sub AppendProcedure(ByVal codeModule As Object, ByVal procedureCode As String)

insert-or-replace-procedure #

Insert or replace a VBA procedure in a code module

Signature:

Private Sub InsertOrReplaceProcedure(ByVal component As Object, _
                                     ByVal procedureName As String, _
                                     ByVal procedureCode As String)

copy-list-objects #

Replicate ListObjects from source to target sheet

Signature:

Private Sub CopyListObjects(ByVal sourceSheet As Worksheet, ByVal targetSheet As Worksheet)

copy-named-ranges #

Copy critical named ranges to the target sheet

Signature:

Private Sub CopyNamedRanges(ByVal sourceSheet As Worksheet, ByVal targetSheet As Worksheet)

log-info #

Append a diagnostic entry to the internal checking log

Signature:

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

Parameters:

label: String. Message describing the event.
scope: Byte. Checking severity. Defaults to checkingNote.
addTimeStamp: Boolean. Whether to prepend a timestamp. Defaults to True.

throw-error #

Raise a typed project error

Signature:

Private Sub ThrowError(ByVal errNumber As Long, ByVal message As String)

Used in (51 file(s))