DesignerPreparation
Prepares a designer workbook for ribbon interactions. The full preparation sequence imports translation tables from a user-selected workbook, creates a dropdown worksheet with standard dropdown lists (setup languages, interface languages, epiweek start, design values), hides internal worksheets (VeryHidden for __pass/__formatter/__formula/ __dropdowns, Hidden for translation sheets), seeds workbook- and worksheet-level HiddenNames flags, applies data validation to the T_Multi table on the GenerateMultiple worksheet, and registers hidden sheets with the development manager. Also provides methods to read and write ribbon checkbox flags persisted through workbook-level hidden names.
Depends on: HiddenNames, IHiddenNames, IDevelopment, OSFiles, IOSFiles, CustomTable, ICustomTable, BetterArray, DropdownLists, IDropdownLists
Factory
create #
Create and configure a designer preparation helper
Signature:
Public Function Create(ByVal hostBook As Workbook) As IDesignerPreparation
Factory method that creates a new DesignerPreparation instance, binds it to the supplied designer workbook via Configure, and returns the result through the IDesignerPreparation interface. Called on the predeclared instance.
Parameters:
hostBook: Workbook. The designer workbook to prepare.
Returns: IDesignerPreparation. A fully initialised instance ready for use.
Public API
configure #
Bind the helper to the designer workbook
Signature:
Public Sub Configure(ByVal hostBook As Workbook)
Stores a reference to the host workbook and clears any previously cached hidden names store and dropdown manager so they will be rebuilt on next access. Raises an error when the workbook is Nothing.
Parameters:
hostBook: Workbook. The designer workbook to bind to.
Throws:
- ProjectError.ObjectNotInitialized When the workbook is Nothing.
prepare #
Execute all preparation tasks on the designer workbook
Signature:
Public Sub Prepare(ByRef devManager As IDevelopment)
Runs the full preparation sequence:
- Prompts the user to select a translations workbook and imports translation tables into LinelistTranslation and DesignerTranslation.
- Creates the dropdown worksheet and registers all standard dropdown lists (setup languages, interface languages, epiweek start, design).
- Hides internal worksheets (VeryHidden for __pass/__formatter/ __formula/__dropdowns, Hidden for LinelistTranslation, DesignerTranslation).
- Seeds workbook-level HiddenNames flags (ribbon checks, language codes, last opened date).
- Seeds Geo worksheet-level HiddenNames flags (geo language, name, update status).
- Applies data validation to the T_Multi table on GenerateMultiple. When devManager is provided, hidden sheets are also registered with the manager for deployment.
Parameters:
devManager: IDevelopment. ByRef development manager instance, or Nothing.
set-flag #
Persist a ribbon checkbox flag as Yes/No in hidden names
Signature:
Public Sub SetFlag(ByVal nameId As String, ByVal enabled As Boolean)
Writes a boolean value to the workbook-level hidden names store as "Yes" or "No". Ensures the store and default flags exist before writing.
Parameters:
nameId: String. The hidden name identifier for the flag (e.g. "chkAlert").enabled: Boolean. True to store "Yes", False to store "No".
get-flag #
Read a ribbon checkbox flag as a boolean from hidden names
Signature:
Public Function GetFlag(ByVal nameId As String, Optional ByVal defaultValue As Boolean = False) As Boolean
Reads the named value from the hidden names store and returns True when the stored value equals "Yes" (case-insensitive comparison). Falls back to the defaultValue when the name is missing.
Parameters:
nameId: String. The hidden name identifier for the flag.defaultValue: Optional Boolean. Value returned when the flag is missing. Defaults to False.
Returns: Boolean. True when the stored value is "Yes".
host-workbook #
The configured host workbook
Signature:
Public Property Get HostWorkbook() As Workbook
Returns the designer workbook that was bound via Configure. Raises an error when accessed before configuration.
Returns: Workbook. The host workbook.
Throws:
- ProjectError.ObjectNotInitialized When the workbook was not configured.
hidden-store #
The workbook-level hidden names manager
Signature:
Public Property Get HiddenStore() As IHiddenNames
Returns the IHiddenNames instance lazily created from the host workbook. Used internally to persist and retrieve ribbon flag values.
Returns: IHiddenNames. The hidden names manager.
dropdowns #
The dropdown manager created during preparation
Signature:
Public Property Get Dropdowns() As IDropdownLists
Returns the IDropdownLists manager bound to the designer dropdown worksheet. Lazily initialises the manager on first access if it was not already created by EnsureDropdowns or Prepare.
Returns: IDropdownLists. The dropdown manager instance.
Internal members (not exported)
Factory
self #
Current object instance
Signature:
Public Property Get Self() As IDesignerPreparation
Convenience accessor so consuming code can fluently retrieve the interface reference from the predeclared Create method.
Returns: IDesignerPreparation. The current instance cast to the interface.
Preparation Helpers
import-translations #
Import translation tables from a user-selected workbook
Signature:
Private Sub ImportTranslations()
Prompts the user to select an xlsx file, then iterates through LinelistTranslation and DesignerTranslation sheets. For each sheet, imports matching ListObjects via CustomTable.Import. This is the same logic as the clickImpTrans ribbon callback, encapsulated here so it runs as part of the preparation sequence.
ensure-dropdowns #
Create the dropdown worksheet and register all standard dropdown lists
Signature:
Private Sub EnsureDropdowns()
Creates the dropdown manager if needed, then registers setup languages (empty placeholder), interface languages, epiweek start (1-7), and design values dropdown lists.
hide-worksheets #
Hide internal worksheets and register them with the dev manager
Signature:
Private Sub HideWorksheets(ByRef devManager As IDevelopment)
Sets __pass, __formatter, __formula, __dropdowns to xlSheetVeryHidden. Sets LinelistTranslation and DesignerTranslation to xlSheetHidden (visible in the VBA editor for developers). When a development manager is provided, all sheets are also registered via AddHiddenSheet for deployment. Silently skips any worksheet that does not exist.
Parameters:
devManager: IDevelopment. Development manager for deployment registration, or Nothing.
ensure-workbook-flags #
Ensure all workbook-level HiddenNames flags exist
Signature:
Private Sub EnsureWorkbookFlags()
Creates workbook-level HiddenNames for ribbon checkbox flags, language codes, and the last opened date. Checkbox flags default to "Yes" (on). Language codes default to empty strings. Last opened date is set to the current date.
ensure-geo-flags #
Ensure Geo worksheet-level HiddenNames flags exist
Signature:
Private Sub EnsureGeoFlags()
Creates worksheet-level HiddenNames on the Geo worksheet for language code, geobase name, metadata language, update status, and form loaded state. Also creates RNG_PastingGeoCol as a real cell-based named range (not a HiddenName) because it is used as a paste anchor at runtime. Silently exits when the Geo worksheet does not exist in the host workbook.
Dropdown Helpers
ensure-dropdown-manager #
Ensure the dropdown manager is initialised for the target workbook
Signature:
Private Sub EnsureDropdownManager()
Exits immediately when the manager is already initialised. Otherwise resolves or creates the dropdown worksheet, then creates a DropdownLists instance bound to it with the standard dropdown prefix.
ensure-dropdown-sheet #
Ensure the dropdown worksheet exists and cache the reference
Signature:
Private Function EnsureDropdownSheet() As Worksheet
Resolves the dropdown worksheet from the host workbook. If not found, creates a new worksheet at the end and names it. Caches the reference for subsequent calls.
Returns: Worksheet. The dropdown worksheet.
register-all-dropdowns #
Register every standard dropdown list for designer worksheets
Signature:
Private Sub RegisterAllDropdowns()
Calls RegisterDropdown for each predefined dropdown: setup languages (empty placeholder for later population), interface languages (pre-populated with the five supported languages), epiweek start (numbers 1 through 7), and design values (matching LLFormat design constants).
register-dropdown #
Register a single dropdown by converting the values into a BetterArray
Signature:
Private Sub RegisterDropdown(ByVal dropdownName As String, ByVal values As Variant)
Ensures the dropdown manager is initialised, converts the supplied Variant array into a BetterArray (1-based), and adds it to the dropdown manager under the given name.
Parameters:
dropdownName: String. The name to register the dropdown under.values: Variant. An array of values to populate the dropdown.
Multi Validations
apply-multi-validations #
Apply data validation to the T_Multi table on GenerateMultiple
Signature:
Private Sub ApplyMultiValidations()
Resolves the GenerateMultiple worksheet and the T_Multi ListObject, then wires the "language of the interface", "epiweek start", and "design" columns to their corresponding dropdown lists via CustomTable.SetValidation. Silently exits when the worksheet or table does not exist in the host workbook.
Main Validations
apply-main-validations #
Apply dropdown validation to named ranges on the Main worksheet
Signature:
Private Sub ApplyMainValidations()
Resolves the Main worksheet and applies data validation from the registered dropdown lists to three named ranges: RNG_LangSetup (setup language), RNG_DesignLL (design selection), and RNG_LLForm (language of linelist interface). Silently exits when the Main worksheet does not exist or individual named ranges are missing.
apply-range-validation #
Apply dropdown validation to a single named range
Signature:
Private Sub ApplyRangeValidation(ByVal sh As Worksheet, ByVal rngName As String, _
ByVal drop As IDropdownLists, ByVal dropName As String)
Resolves the named range from the worksheet and applies list validation using the specified dropdown. Silently exits when the named range does not exist on the worksheet.
Parameters:
sh: Worksheet. The worksheet containing the named range.rngName: String. The named range identifier.drop: IDropdownLists. The dropdown manager with registered lists.dropName: String. The dropdown list name to use as validation source.
Internal Helpers
ensure-hidden-store #
Lazily initialise the hidden names store from the host workbook
Signature:
Private Sub EnsureHiddenStore()
Creates the IHiddenNames instance on first access by calling HiddenNames.Create with the host workbook. Subsequent calls are no-ops.
ensure-default-flags #
Ensure both default ribbon flags exist in the hidden store
Signature:
Private Sub EnsureDefaultFlags()
Calls EnsureName for chkAlert and chkInstruct with "Yes" as default, creating the hidden names if they do not already exist. All checks default to enabled per specification.
resolve-worksheet #
Resolve a worksheet by name from the host workbook
Signature:
Private Function ResolveWorksheet(ByVal sheetName As String) As Worksheet
Returns the worksheet with the given name from the host workbook, or Nothing when the sheet does not exist.
Parameters:
sheetName: String. The worksheet name to resolve.
Returns: Worksheet. The resolved worksheet, or Nothing.
set-sheet-visibility #
Set the visibility of a worksheet by name
Signature:
Private Sub SetSheetVisibility(ByVal sheetName As String, ByVal visibility As Long)
Resolves the worksheet from the host workbook and sets its Visible property. Silently skips when the worksheet does not exist.
Parameters:
sheetName: String. The worksheet name.visibility: Long. The xlSheetVisibility constant to apply.
register-hidden-sheet #
Register a sheet with the development manager for deployment hiding
Signature:
Private Sub RegisterHiddenSheet(ByRef manager As IDevelopment, ByVal sheetName As String)
Wraps manager.AddHiddenSheet in error handling so a missing sheet does not abort the preparation sequence.
Parameters:
manager: IDevelopment. The development manager to register with.sheetName: String. The worksheet name to register.
ensure-geo-range #
Ensure a cell-based named range exists on the Geo worksheet
Signature:
Private Sub EnsureGeoRange(ByVal sh As Worksheet, ByVal rngName As String)
Checks whether the named range already exists on the worksheet. If not, creates the range in a free cell below the used area so it does not overlap with existing ListObjects or other content.
Parameters:
sh: Worksheet. The Geo worksheet.rngName: String. The named range identifier to create.
raise-error #
Raise a ProjectError-based exception
Signature:
Private Sub RaiseError(ByVal errorCode As ProjectError, ByVal messageText As String)
Wrapper around Err.Raise that standardises the source to CLASS_NAME.
Parameters:
errorCode: ProjectError. The error code to raise.messageText: String. Human-readable description of the failure.
Throws:
- ProjectError.
Always raises the specified error.
Interface Implementation
IDesignerPreparation_Configure #
Signature:
Private Sub IDesignerPreparation_Configure(ByVal hostBook As Workbook)
Delegated members satisfying the IDesignerPreparation contract. See the corresponding members above for full documentation.