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.

This class is on the hot path of a whole build, so it reads the worksheet as little as it can. The debugging password and the debug-mode flag are each read once and kept on the instance; ResetCaches drops them, and ResetCaches runs when the sheet is bound and after ImportFrom. The protection table is read as one block and matched in memory.

Excel state belongs to the caller. This class never touches Application.ScreenUpdating, Calculation or EnableEvents.

Depends on: Checking, ProjectError, TranslationObject

Version: 1.1 (2026-07-28)

Core Accessors

Create #

create

Create an instance of password

Signature:

Public Function Create(ByVal sh As Worksheet) As Passwords

Factory returning a password handler bound to the provided worksheet. The sheet is validated first: both tables and the five core named ranges must be there. The instance is sealed, so the setup setter can no longer be written.

Parameters:

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

Returns: Passwords. 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)

Every worksheet in the target workbook is unprotected. LeaveDebugMode restores only the sheets listed in the protection table, so a sheet protected by hand, or protected with registerState:=False, comes back unprotected. That is the contract of this pair.

The debug-mode flag is always written to the bound worksheet, whichever workbook is passed in. Bind the object to the workbook you are toggling.

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)

Only the sheets listed in the protection table come back protected. The debug-mode flag is always written to the bound worksheet, whichever workbook is passed in. Bind the object to the workbook you are toggling.

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)

The reference is built from this object's own protection table. When the target workbook holds no table by that name, the name resolves to #REF! and the generated close handler fails on it. CloneToWorksheet avoids that by calling through the handler bound to the new sheet.

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:

Public 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:

Public 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:

Public Sub GenerateKey(ByVal trads As TranslationObject)

Parameters:

  • trads: TranslationObject. Translation helper for prompts.

DisplayPrivateKey #

display-private-key

Display the current private key via a translated prompt

Signature:

Public Sub DisplayPrivateKey(ByVal trads As TranslationObject)

The dialog is shown only when DisplayPrompts is True, which is the default. The prompt text and title are always recorded, so a caller that suppresses the dialog can still read what would have been shown.

Parameters:

  • trads: TranslationObject. Translation helper for the message.
DataExchange

ExportToWorkbook #

export-to-workbook

Export the password worksheet into another workbook

Signature:

Public Sub ExportToWorkbook(ByVal destination As Workbook)

Parameters:

  • destination: Workbook. Target workbook.

ImportFrom #

import-from

Import keys and metadata from another password handler

Signature:

Public Sub ImportFrom(ByVal source As Passwords)

Three named values move across: the public key, the private key and the version. The debugging password, the lab keys and the protection table stay as they are on this sheet. The destination keys table is resized to the shape of the source table, columns included.

Parameters:

  • source: Passwords. Source password object.

NewDebugingPassword #

new-debuging-password

Update the debugging password stored on the password worksheet

Signature:

Public Sub NewDebugingPassword(ByVal newValue As String)

Raises ElementNotFound when the named range is gone. Create validates that name, so this can only fire when the name was removed after the object was built.

Parameters:

  • newValue: String. The new debugging password value.

CloneToWorksheet #

clone-to-worksheet

Clone the password worksheet into another sheet

Signature:

Public Function CloneToWorksheet(ByVal targetSheet As Worksheet) As Passwords

Parameters:

  • targetSheet: Worksheet. Destination worksheet.

Returns: Passwords. Handler bound to the cloned sheet.

CloneToWorkbook #

clone-to-workbook

Clone the password worksheet into another workbook

Signature:

Public Function CloneToWorkbook(ByVal wb As Workbook) As Passwords

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

Parameters:

  • wb: Workbook. Destination workbook.

Returns: Passwords. Handler bound to the cloned sheet.

Private Helpers

ApplyProtectionMatrix #

apply-protection-matrix

Apply stored protection settings to all listed worksheets

Signature:

Public Sub ApplyProtectionMatrix(ByVal wb As Workbook)

Reads the whole protection table in one go and protects each listed sheet with the same password. A row naming a sheet that was renamed or deleted is written to the checking log, and the remaining rows still get their protection.

Parameters:

  • wb: Workbook. Target workbook whose sheets will be protected.
Internal members (not exported)

Core Accessors

PasswordSheet #

password-sheet-set

Assign the password worksheet and reset caches

Signature:

Public Property Set PasswordSheet(ByVal sh As Worksheet)

Parameters:

Name #

name

Name of the password worksheet

Signature:

Public Property Get Name() As String

The name is taken once, when the sheet is bound. Renaming the sheet afterwards leaves this answer on the old name. Reading this.sh.Name live would cost a crossing on a value that stays the same in practice.

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.

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)

Set this to False to run the key routines without a dialog. A test harness needs it: MsgBox blocks a headless run.

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:

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:

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:

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 Checking

Returns: Checking. 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)

When registerState is True the two capability flags are folded with the values already stored for the sheet, and the result is written back. The fold is one-way: once a sheet is stored as "no" for shapes or for row deletion, no later call widens it back to "yes".

When registerState is False the incoming flags are used as they are and the protection table is left alone.

Parameters:

UnprotectSheet #

unprotect-sheet

Unprotect a worksheet using the stored debugging password

Signature:

Private Sub UnprotectSheet(Optional sheetObject As Variant)

Parameters:

ProtectWorkbook #

protect-workbook

Protect workbook structure with the debugging password

Signature:

Private Sub ProtectWorkbook(targetObject As Variant)

Parameters:

UnprotectWorkbook #

unprotect-workbook

Unprotect workbook structure using the debugging password

Signature:

Private Sub UnprotectWorkbook(targetObject As Variant)

Parameters:

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. Nothing shares the workbook branch, where ResolveWorkbook turns it back into the bound workbook.

This is the one place the protect/unprotect choice is made. Protect and UnProtect both come through here, so the two paths cannot drift apart.

Parameters:

Private Helpers

ValidateSheet #

validate-sheet

Validate the password worksheet

Signature:

Private Sub ValidateSheet(ByVal sh As Worksheet)

The two lab key names are left out on purpose: password sheets built before those names exist still work. CopyNamedRanges skips whichever of them is missing and records the gap.

EnsureSheetReady #

ensure-sheet-ready

Ensure password worksheet is initialised

Signature:

Private Sub EnsureSheetReady()

this.wb is written on the same statement as this.sh, and Create is the only way in, so a ready sheet always carries a ready workbook.

ResetCaches #

reset-caches

Clear the cached tables, password and debug flag

Signature:

Private Sub ResetCaches()

SheetTable #

sheet-table

Resolve a ListObject on a worksheet

Signature:

Private Function SheetTable(ByVal targetSheet As Worksheet, ByVal tableName As String) As ListObject

Parameters:

Returns: ListObject. The resolved table.

CachedTable #

cached-table

Resolve a table on the bound sheet and remember it

Signature:

Private Function CachedTable(ByVal tableName As String, ByRef cache As ListObject) As ListObject

Parameters:

Returns: ListObject. The resolved table.

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

Signature:

Private Function NamedRange(ByVal rangeName As String, _
                            Optional ByVal targetSheet As Worksheet) As Range

Resolves against the bound worksheet, or against targetSheet when one is passed. Create validates the sheet before it is bound, which is what the second argument is for.

Parameters:

Returns: Range. The named range.

DebugPassword #

debug-password

Read the debugging password, once per object

Signature:

Private Function DebugPassword() As String

The password is one cell that changes at most once in a session, and every protect and unprotect call needs it. It is read on the first call and kept. ResetCaches and NewDebugingPassword drop the copy.

Returns: String. The debugging password.

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.

IsInDebugMode #

is-in-debug-mode

Test whether the workbook is in debug mode

Signature:

Public Function IsInDebugMode() As Boolean

The flag is one cell on the bound sheet, and ProtectSheet asks for it on every call. It is read once and kept. EnterDebugMode and LeaveDebugMode are the two writers and both refresh the copy; ResetCaches drops it.

Returns: Boolean. True when debug mode is active.

SetDebugModeFlag #

set-debug-mode-flag

Write the debug-mode flag and refresh the cached copy

Signature:

Private Sub SetDebugModeFlag(ByVal active As Boolean)

Parameters:

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)

UserInterfaceOnly keeps macros able to write to the sheet. Excel drops that flag when the file closes, so a reopened workbook has to run ApplyProtectionMatrix again before code writes anywhere.

ReadProtectionFlags #

read-protection-flags

Read the stored capability flags for a worksheet

Signature:

Private Sub ReadProtectionFlags(ByVal sheetName As String, _
                                ByRef allowShapes As Boolean, _
                                ByRef allowDeletingRows As Boolean, _
                                ByRef rowIndex As Long)

The protection table is read as one block and matched in memory, so a lookup costs one crossing. Sheet names are matched without regard to case, the way Excel itself treats them, so "Data" and "data" resolve to the same row.

Parameters:

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, _
                                      ByVal rowIndex As Long)

Parameters:

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)

The two lab key names are younger than the other five, so a password sheet built before them passes ValidateSheet without them. Each missing name is skipped and written to the checking log, which keeps an old password sheet usable at build time.

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:

ThrowError #

throw-error

Raise a typed project error

Signature:

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

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 (44 file(s))