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

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

EnterDebugMode #

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.

LeaveDebugMode #

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.

EnsureProtectedSheetsName #

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.

EnsureDebugExitHandler #

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

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

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

GenerateKey #

generate-key

Randomly select a public/private key pair

Signature:

Private Sub GenerateKey(ByVal trads As ITranslationObject)

Parameters:

trads: ITranslationObject. Translation helper for prompts.

DisplayPrivateKey #

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

ExportToWorkbook #

export-to-workbook

Export the password worksheet into another workbook

Signature:

Private Sub ExportToWorkbook(ByVal destination As Workbook)

Parameters:

destination: Workbook. Target workbook.

ImportFrom #

import-from

Import keys and metadata from another password handler

Signature:

Private Sub ImportFrom(ByVal source As IPasswords)

Parameters:

source: IPasswords. Source password object.

UpdateDebugingPassword #

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.

CloneToWorksheet #

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.

CloneToWorkbook #

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

ApplyProtectionMatrix #

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

Self-reference as IPasswords

Signature:

Public Property Get Self() As IPasswords

Returns: IPasswords. Self-reference.

PasswordSheet #

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

Name of the password worksheet

Signature:

Public Property Get Name() As String

Returns: String. The worksheet name.

PasswordSheet #

password-sheet

Worksheet hosting the password tables

Signature:

Public Property Get PasswordSheet() As Worksheet

Returns: Worksheet. The password worksheet.

PasswordWorkbook #

password-workbook

Workbook owning the password worksheet

Signature:

Public Property Get PasswordWorkbook() As Workbook

Returns: Workbook. The parent workbook.

DisplayPrompts #

display-prompts

Whether UI prompts are displayed during operations

Signature:

Public Property Get DisplayPrompts() As Boolean

Returns: Boolean. True when prompts are enabled.

DisplayPrompts #

display-prompts-set

Toggle UI prompt display

Signature:

Public Property Let DisplayPrompts(ByVal value As Boolean)

LastPrivatePrompt #

last-private-prompt

Most recent private key prompt text

Signature:

Public Property Get LastPrivatePrompt() As String

Returns: String. The prompt text.

LastPrivatePromptTitle #

last-private-prompt-title

Most recent private key prompt title

Signature:

Public Property Get LastPrivatePromptTitle() As String

Returns: String. The prompt title.

Value #

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.

TagValue #

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.

TableRange #

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.

HasCheckings #

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.

CheckingValues #

checking-values

Retrieve the aggregated checking entries

Signature:

Public Property Get CheckingValues() As IChecking

Returns: IChecking. The checking object, or Nothing.

Protection

ProtectSheet #

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.

UnprotectSheet #

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.

ResolveWorksheetProtection #

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.

ProtectWorkbook #

protect-workbook

Protect workbook structure with the debugging password

Signature:

Private Sub ProtectWorkbook(targetObject As Variant)

Parameters:

targetObject: Variant. Workbook reference.

UnprotectWorkbook #

unprotect-workbook

Unprotect workbook structure using the debugging password

Signature:

Private Sub UnprotectWorkbook(targetObject As Variant)

Parameters:

targetObject: Variant. Workbook reference.

ResolveWorkbookProtection #

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.

ResolveProtection #

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

ValidateSheet #

validate-sheet

Validate the password worksheet

Signature:

Private Sub ValidateSheet(ByVal sh As Worksheet)

EnsureSheetReady #

ensure-sheet-ready

Ensure password worksheet is initialised

Signature:

Private Sub EnsureSheetReady()

EnsureWorkbookReady #

ensure-workbook-ready

Ensure the password workbook reference is resolved

Signature:

Private Sub EnsureWorkbookReady()

EnsureLoExists #

ensure-lo-exists

Validate that a ListObject exists on a worksheet

Signature:

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

ResetCaches #

reset-caches

Clear cached ListObject references

Signature:

Private Sub ResetCaches()

KeysTable #

keys-table

Retrieve and cache the keys ListObject

Signature:

Private Function KeysTable() As ListObject

Returns: ListObject. The keys table.

ProtectedSheetsTable #

protected-sheets-table

Retrieve and cache the protected-sheets ListObject

Signature:

Private Function ProtectedSheetsTable() As ListObject

Returns: ListObject. The protection table.

NamedRange #

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.

ResolveWorkbook #

resolve-workbook

Resolve a workbook from optional candidate

Signature:

Private Function ResolveWorkbook(Optional candidate As Variant) As Workbook

Returns: Workbook. The resolved workbook.

ResolveSheet #

resolve-sheet

Resolve a worksheet from a Variant argument

Signature:

Private Function ResolveSheet(sheetObject As Variant) As Worksheet

Returns: Worksheet. The resolved worksheet.

TagToRangeName #

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.

TagFromName #

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.

EnsureRangeExists #

ensure-range-exists

Validate that a named range exists on a worksheet

Signature:

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

InDebugMode #

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.

ApplySheetProtection #

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)

ProtectionRecord #

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

EmptyProtectionRecord #

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

PersistProtectionSettings #

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)

BooleanToFlag #

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

ParseBoolean #

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

BuildLeaveDebugProcedure #

build-leave-debug-procedure

Generate VBA code for the LeaveDebugModeOnClose procedure

Signature:

Private Function BuildLeaveDebugProcedure() As String

Returns: String. VBA source code.

BuildBeforeCloseProcedure #

build-before-close-procedure

Generate VBA code for the Workbook_BeforeClose handler

Signature:

Private Function BuildBeforeCloseProcedure() As String

Returns: String. VBA source code.

EnsureWorkbookBeforeCloseHook #

ensure-workbook-before-close-hook

Ensure Workbook_BeforeClose invokes the debug guard

Signature:

Private Sub EnsureWorkbookBeforeCloseHook(ByVal component As Object)

ProcedureBlockContainsToken #

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.

AppendProcedure #

append-procedure

Append generated code to the end of a code module

Signature:

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

InsertOrReplaceProcedure #

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)

CopyListObjects #

copy-list-objects

Replicate ListObjects from source to target sheet

Signature:

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

CopyNamedRanges #

copy-named-ranges

Copy critical named ranges to the target sheet

Signature:

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

LogInfo #

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.

ThrowError #

throw-error

Raise a typed project error

Signature:

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

Used in (53 file(s))