EventLinelist
Encapsulates workbook-level linelist events and lazily-cached domain managers. This class is the single entry point for all event handling in the output linelist workbook. It reacts to sheet change (HList geo cascades, editable labels, list auto, go-to-section, multiple choices; VList editable labels and multiple choices; analysis go-to-section and spatial dispatch), sheet deactivate (list auto update), selection change (geo dropdown refresh), double-click (spatio-temporal geo form), filter table updates, list-auto dropdown maintenance, and geo/spatial UDF delegation.
Depends on: BetterArray, HiddenNames, DropdownLists, LLGeo, LLSpatial, LLdictionary, LLVariables, LLTranslation, TranslationObject, Passwords, LLLog, FilteredData, ProjectError
Factory
Create #
Build a configured EventLinelist instance bound to a host workbook.
Signature:
Public Function Create(ByVal hostBook As Workbook) As EventLinelist
Parameters:
hostBook: Workbook. The linelist workbook.
Returns: EventLinelist. Configured service instance.
Throws:
- ProjectError.ObjectNotInitialized When hostBook is Nothing.
Public operations
Configure #
Configure the service with its host workbook context.
Signature:
Public Sub Configure(ByVal hostBook As Workbook)
Parameters:
hostBook: Workbook. The linelist workbook.
Throws:
- ProjectError.ObjectNotInitialized When hostBook is Nothing.
OnWorkbookOpen #
React to workbook open notifications.
Signature:
Public Sub OnWorkbookOpen()
The open is the first line of the session in the user log, so a reader of the log sees where each working session started.
The workbook opens on the sheet it was saved on, and a linelist saved by the generation lands on Geo. LandOnInstructions puts the user on the instruction sheet instead, every open.
The pointer is parked on the north-west arrow at open. That arrow is the cursor every busy state of the session shows, and ApplicationState restores the cursor it snapshots, so with the standing cursor equal to the busy one an event leaves no visible pointer change. The snapshot used to hold the default cursor, and every selection on a data entry sheet flicked the pointer twice.
OnSheetChange #
Handle worksheet change events.
Signature:
Public Sub OnSheetChange(ByVal sh As Worksheet, ByVal target As Range)
Parameters:
sh: Worksheet. The worksheet raising the change event.target: Range. The edited cells.
OnSheetDeactivate #
Handle worksheet deactivation events.
Signature:
Public Sub OnSheetDeactivate(ByVal prevsh As Worksheet)
The handler is written into the code module of each HList sheet, so the sheet that was left arrives as an object and its own flag is all this reads.
Rebuilding an automatic list means reading a whole column, uniquing it and writing a table, per list, and it is far too slow to do on the way out of every sheet. It happens only when something the lists depend on was edited on THIS sheet, which is what the flag records and what the sheet-change handler raises. A sheet nobody touched costs one hidden-name read on the way out.
Parameters:
prevsh: Worksheet. The sheet that was deactivated.
OnSelectionChange #
Handle selection change events on HList sheets.
Signature:
Public Sub OnSelectionChange(ByVal sh As Worksheet, ByVal target As Range)
The sheet is an HList sheet by construction. Worksheet_SelectionChange is written into the code module of the HList sheets alone, so a "sheet_type" read here can only ever answer HList, and it cost a walk of the sheet Names collection on every arrow key. The handler below wants the store, and reads the held one.
Parameters:
sh: Worksheet. The worksheet where the selection changed.target: Range. The newly selected cell(s).
OnDoubleClick #
Handle double-click events on analysis sheets.
Signature:
Public Function OnDoubleClick(ByVal sh As Worksheet, ByVal target As Range) As Long
Answers the geo scope whose picker the double-click asked for, and GEOSCOPENONE when the click asked for no picker at all. The caller is what opens the picker: the form lives in the workbook and a class cannot reach it.
Parameters:
sh: Worksheet. The worksheet where the double-click occurred.target: Range. The double-clicked cell.
Returns: Long. GeoScopeAdmin, GeoScopeHF, or GEOSCOPENONE.
UpdateFilterTables #
Update filter tables used by analysis sheets.
Signature:
Public Sub UpdateFilterTables(Optional ByVal calculate As Boolean = True)
FilteredData does the walk and the rewrite: every filtered table is written from the visible rows of its source table that hold a value. The class is the measured home of that work, and the filtered export runs the same code, so one algorithm answers both callers.
A sheet the sync could not place is skipped and named. The walk finishes the remaining sheets and the box at the foot lists each skipped sheet with its reason, which is what a user needs to repair the workbook.
FilteredData writes the failure line of the run to the user log, so the box here goes straight through ShowMessage and the log keeps one line per failed refresh.
The recalculation at the foot is one pass: the sheet pass covers the UsedRange and column passes that used to follow it.
Parameters:
calculate: Optional Boolean. When True (default), recalculates the active sheet.
UpdateAllListAuto #
Update all list-auto dropdowns across all HList sheets.
Signature:
Public Sub UpdateAllListAuto()
RecalculateGeoColumns #
Recalculate every cell whose formula reads the geobase.
Signature:
Public Sub RecalculateGeoColumns()
A geobase import rewrites the Geo sheet while the workbook runs on manual calculation, so the cells built on it keep the values of the geobase before. Three families of cells do: the concat columns of the data entry sheets, the p-code columns beside them, and the four admin level labels the spatial analysis dropdowns list. The geobase import handler calls this once the Geo sheet is rewritten and the caches are dropped.
The label cells matter twice over. They are what the dropdown shows, and LLGeo.AdminCode matches the picked label against the fresh level names, so a stale label resolves to no level and stops every admin level change on the sheet.
Translation #
The one translation helper of the workbook.
Signature:
Public Function Translation() As LLTranslation
The button, ribbon and geo modules each used to build their own. LLTranslation validates all five tables on Create and caches every scope it is asked for until Refresh, so one instance answers all four callers for the price of the first build, and Refresh then has one place to be called from. A module reaches this through LinelistEventsManager.EventLinelistService.
Returns: LLTranslation. The held helper, or Nothing when the workbook carries no usable translation sheet.
WorkbookNames #
The hidden names of the workbook, built once and held.
Signature:
Public Function WorkbookNames() As HiddenNames
The button module used to build its own on every click, and a workbook store costs a walk of the whole Names collection.
Returns: HiddenNames. The held store, or Nothing when the build failed.
SheetNames #
The hidden names of one worksheet, built once and held.
Signature:
Public Function SheetNames(ByVal sh As Worksheet) As HiddenNames
One store serves the event handlers and the buttons together, and the class drops it when that sheet raises a change. The button module read a sheet tag, a table name and a blank row count through three separate stores.
Parameters:
sh: Worksheet. The worksheet whose names are wanted.
Returns: HiddenNames. The held store, or Nothing when the build failed.
PasswordManager #
The password manager of the workbook, built once and held.
Signature:
Public Function PasswordManager() As Passwords
Returns: Passwords. The held manager, or Nothing when the build failed.
UserLog #
The user log of the workbook, built once and held.
Signature:
Public Function UserLog() As LLLog
The dated record of the user actions, kept on the very hidden log worksheet. Fail and Warn write their line through it, and the button and form modules write their outcome lines through it. An old linelist grows the log sheet on the first logged event.
Returns: LLLog. The held log, or Nothing when the build failed.
Dictionary #
The dictionary of the linelist, built once and held.
Signature:
Public Function Dictionary() As LLdictionary
The variable-labels button used to build a dictionary per click, and creating one walks the whole Dictionary worksheet. A workbook with no Dictionary sheet answers Nothing, and the tried flag keeps the failed build from repeating.
Returns: LLdictionary. The held dictionary, or Nothing when the build failed.
Variables #
The variable reader of the dictionary, built once and held.
Signature:
Public Function Variables() As LLVariables
Returns: LLVariables. The held reader, or Nothing when the build failed.
GeoManager #
The geobase manager of the workbook, built once and held.
Signature:
Public Function GeoManager() As LLGeo
The geo and form modules used to build one each, and LLGeo.Create walks two whole Names collections per build. A held instance also caches the five admin level labels, so anything that rewrites the Geo sheet has to be followed by ResetCaches for the labels to be read again. HandleImportGeobase already does that on both its paths.
Returns: LLGeo. The held manager, or Nothing when the build failed.
VarLabelTable #
The table the variable-labels form shows.
Signature:
Public Function VarLabelTable() As BetterArray
One row per hlist2D variable: the pivot block title of its table, the variable name and the main label. The pivot titles are worksheet hidden names CustomPivotTable writes on the custom pivot sheet, and that sheet is reached through the workbook hidden name RNG_CustomPivot. A workbook where the dictionary or the pivot sheet cannot be read answers what it can: an empty table, or rows with empty titles.
The rows are built in memory. The button used to stage them on the __temp worksheet and read them back, one Excel crossing per cell.
Returns: BetterArray. One item per variable, each an array of title, name and label.
ResetCaches #
Clear cached resources.
Signature:
Public Sub ResetCaches()
Messages
Warn #
Tell the user why a button refused to act.
Signature:
Public Sub Warn(ByVal msgCode As String, _
Optional ByVal source As String = vbNullString)
The refusal is also one warning line of the user log, written before the box shows. Every refusal box of the button, ribbon and geo modules comes through here, so the log covers them all through this one edit. The line carries the sentence the user was shown, in the language they were shown it in, and the procedure that refused. It used to carry the message CODE as the action and nothing else, so a log read weeks later said "MSG_SectionTitleCell" and named neither the reason nor the button.
Parameters:
msgCode: String. Code of the message in the message translation table.source: String. Optional. The procedure raising the refusal.
Fail #
Tell the user an operation failed.
Signature:
Public Sub Fail(ByVal msgCode As String, _
Optional ByVal detail As String = vbNullString, _
Optional ByVal fallback As String = vbNullString, _
Optional ByVal source As String = vbNullString, _
Optional ByVal logDetail As String = vbNullString)
The failure is also one failure line of the user log, written before the box shows. Every failure box of the button, ribbon and geo modules comes through here, so the log covers them all through this one edit. The line reads as the box did: the translated sentence, then the detail behind it, and the procedure that failed in front. A reason meant for the log alone goes in logDetail. A raw Err.Description is worth having in a log a user sends on and is worth little inside a box on a field machine, and several handlers have a reason of exactly that kind.
Parameters:
msgCode: String. Code of the message in the message translation table.detail: String. Optional. Follows the message in the box AND the log.fallback: String. Optional. Shown when the workbook carries no usable translation sheet. Leave it off to stay quiet in that state.source: String. Optional. The procedure raising the failure.logDetail: String. Optional. Follows the message in the log alone.
Geo / Spatial UDF delegation
GeoConcat #
Concatenate geo cells up to a given admin level.
Signature:
Public Function GeoConcat(ByVal cellRng As Range, ByVal geoLevel As Byte) As String
Parameters:
cellRng: Range. The first geo cell.geoLevel: Byte. Number of admin levels to concatenate (1-4).
Returns: String. Pipe-delimited concatenation of non-empty admin values.
BareSpatialName #
Take the authored spatial tag off a variable name.
Signature:
Public Function BareSpatialName(ByVal varName As String) As String
The spatial formulas carry the dictionary variable the tables were summed
over -- "concat_adm
The geographic tag is matched at every level. The formulas are authored on the adm1 spelling, and LLSpatial.ChangeAdminLevel rewrites the level inside the quoted argument when the user picks another one. The strip used to know "concat_adm1_" alone, so the first level change left the tag on, the table lookup missed, and FindTopAdmin answered nothing for the rest of the session.
Parameters:
varName: String. Variable name as the formula carries it.
Returns: String. The bare variable name.
TopAdmin #
Return the top admin name for a spatial variable.
Signature:
Public Function TopAdmin(ByVal adminLevel As String, _
ByVal adminOrder As Integer, _
ByVal varName As String, _
Optional ByVal tabId As String = vbNullString) As String
Parameters:
adminLevel: String. The level label as the dropdown carries it (e.g. "District").adminOrder: Integer. Rank order (1 = top, 2 = second, etc.).varName: String. Variable name (with prefix, e.g. "concat_adm1_myvar").tabId: Optional String. Spatial table identifier.
Returns: String. Admin name at the given rank.
TopPop #
Return the population for the top admin of a spatial variable.
Signature:
Public Function TopPop(ByVal adminLevel As String, _
ByVal adminOrder As Integer, _
ByVal varName As String, _
Optional ByVal tabId As String = vbNullString) As Long
Parameters:
adminLevel: String. The level label as the dropdown carries it (e.g. "District").adminOrder: Integer. Rank order (1 = top, 2 = second, etc.).varName: String. Variable name (with prefix "concat_adm1_").tabId: Optional String. Spatial table identifier.
Returns: Long. Population count at the given rank.
TopHF #
Return the top health facility name for a spatial variable.
Signature:
Public Function TopHF(ByVal adminOrder As Integer, _
ByVal varName As String, _
Optional ByVal tabId As String = vbNullString) As String
Parameters:
adminOrder: Integer. Rank order (1 = top, 2 = second, etc.).varName: String. Variable name (with "hf_" prefix).tabId: Optional String. Spatial table identifier.
Returns: String. Health facility name at the given rank.
Internal members (not exported)
Public operations
LandOnInstructions #
Put the user on the instruction sheet.
Signature:
Private Sub LandOnInstructions()
The sheet is named by the translation, and the generation moves it to the front of the workbook, so the first visible worksheet is the same sheet whenever the name cannot be read. A workbook built with the instructions turned off carries the sheet very hidden, and the fallback then lands on the first sheet the user is meant to see rather than on Geo.
Nothing here is worth an error box on open. Every step is guarded and a workbook that answers none of them opens where it was saved.
RecalculateGeoColumnsOfSheet #
Recalculate the geobase columns of one data entry sheet.
Signature:
Private Sub RecalculateGeoColumnsOfSheet(ByVal sh As Worksheet)
Walks the header row of the data table and recalculates the data body of every concat and p-code column. The concat columns go first: the p-code lookups match on them. A sheet with no table or no data row is left alone.
Parameters:
sh: Worksheet. The HList worksheet.
Messages
MessageText #
The sentence one message code stands for.
Signature:
Private Function MessageText(ByVal msgCode As String) As String
The same reading ShowMessage makes, so the log line and the box say the same thing. A workbook with no usable translation sheet, and a code the table has no row for, both answer the code itself: a log naming a code is worth more than a log naming nothing.
Parameters:
msgCode: String. Code of the message in the message translation table.
Returns: String. The translated sentence, or the code.
JoinedDetail #
Put the detail behind the message, the way the box does.
Signature:
Private Function JoinedDetail(ByVal message As String, _
ByVal detail As String) As String
Parameters:
message: String. The translated sentence.detail: String. Free text following it. May be empty.
Returns: String. The two joined, or the message alone.
ShowMessage #
Show one box, translated when a translator is there.
Signature:
Private Sub ShowMessage(ByVal msgCode As String, _
ByVal boxStyle As Long, _
ByVal detail As String, _
ByVal fallback As String)
A workbook with no usable translation sheet has no text to show, and the callers that still have something to say pass a fallback. Every other caller stays quiet, which is what the two boxes this replaced did.
LogEventLine #
Write one line of the user log.
Signature:
Private Sub LogEventLine(ByVal scope As Byte, _
ByVal action As String, _
ByVal detail As String, _
Optional ByVal source As String = vbNullString)
A log write must never take down the box or the walk it records, so a workbook whose log cannot be built stays quiet and a write that raises is swallowed here.
Parameters:
scope: Byte. CheckingScope value carrying the outcome.action: String. The action code, used as the bundle title.detail: String. Short free text beside the timestamp.source: String. Optional. The procedure that raised the event.
Geo / Spatial UDF delegation
RecordedAdminCode #
The admin level a spatial table's formulas stand on.
Signature:
Private Function RecordedAdminCode(ByVal tabId As String) As String
The dropdown label is the first source of the level and this cell is the second. A freshly generated table carries the "select a level" prompt in its dropdown, a user can clear the dropdown with Delete, and a geobase re-imported in another language leaves the cell holding a label no level answers to. In all three the label resolves to nothing while this cell still records the level the formulas of that table were last written against, so the reads stay on the level the sheet is actually showing.
Parameters:
tabId: String. The identifier of the analysis table.
Returns: String. The recorded admin code, or an empty string when the table carries no such cell.
Private -- HList change handler
HandleHListChange #
Handle value changes on HList sheets.
Signature:
Private Sub HandleHListChange(ByRef sh As Worksheet, ByRef target As Range, _
ByRef shHn As HiddenNames)
Dispatches on the position of the edited cell, cheapest and most specific test first, and every branch exits. An edit inside the table recalculates its own row and goes to the geo cascade, the list-auto flag or the multiple choice toggle; an edit on the label row updates the editable label; an edit on the go-to cell navigates to the chosen section; an edit on the header row is refused and the cell restored.
THE GO-TO TEST READS THE FIRST CELL AND THE SHEET STORE
The builder puts the go-to dropdown in the first cell of the sheet and stores its caption in the sheet store under