ShowHideLayout

HList, Printed a column VList, CRF a row

A CRF holds one variable per row and reads its position from crf index, its own dictionary column.

Every method that writes to the sheet unprotects it first and protects it again after. Reads leave the protection alone. A loop that writes many positions calls BeginBatch first and EndBatch at the end, which holds one bracket open over the whole run.

Excel reports a hidden column as width 0 and hides a column that is set to width 0. SetSize refuses anything at or below zero. Hiding is SetHidden's work, and a blank size cell leaves the sheet alone.

SetHidden reads the size before it hides and keeps it on the instance, keyed on the position. SizeWhenShown answers from that and shows a column only when it holds nothing for it. WriteCount says how many writes went out since the bind or the last ResetWriteCount, so a save can tell a session that moved nothing from one that did.

Depends on: Passwords, Checking, The named cell a printed sheet puts at the head of its table, How far above the anchor the printed header row sits. PRINTSTART is on the, first line of the table, so the header the user reads is the line above it., The row height a turned header needs to stay readable

Version: 1.1 (2026-09-01)

Create #

create

Bind a layout to one worksheet of one layer

Signature:

Public Function Create(ByVal sh As Worksheet, _
                       ByVal layer As Byte, _
                       Optional ByVal passManager As Passwords, _
                       Optional ByVal baseTableName As String = vbNullString) As ShowHideLayout

The factory.

Parameters:

• sh: Worksheet. The sheet the entries live on.
• layer: Byte. A ShowHideWorksheetLayer value. Which layer the sheet belongs to.
• passwords: Optional Passwords. The protection keys of the workbook. The layout writes without a protection bracket when it has none.
• baseTableName: Optional String. The table name with no print_ prefix, which is what the PRINTSTART anchor is named after. Only the printed layer reads it.

Returns: ShowHideLayout. The layout, ready to read and write.

Throws:

• ProjectError.ObjectNotInitialized When the worksheet is Nothing.
• ProjectError.InvalidArgument When the layer is outside the four values.

---

Bind #

bind

Take the four things the layout works with

Signature:

Public Sub Bind(ByVal sh As Worksheet, _
                ByVal layer As Byte, _
                ByVal passManager As Passwords, _
                ByVal baseTableName As String)

Public because the factory calls it on a fresh instance.

Parameters:

• sh: Worksheet. The sheet the entries live on.
• layer: Byte. A ShowHideWorksheetLayer value. The layer of the sheet.
• passwords: Passwords. The protection keys, or Nothing.
• baseTableName: String. The table name with no print_ prefix.

---

Wksh #

wksh

The worksheet this layout writes to

Signature:

Public Property Get Wksh() As Worksheet

What the layout is bound to.

---

Layer #

layer

The layer this layout was built for

Signature:

Public Property Get Layer() As Byte

---

Axis #

axis

Whether this layer hides rows or columns

Signature:

Public Property Get Axis() As Byte

---

SupportsOrientation #

supports-orientation

Whether the sheet has a header that can be turned

Signature:

Public Property Get SupportsOrientation() As Boolean

True on a printed sheet alone. A data entry sheet, a VList and a CRF all read their labels straight, so turning a header means nothing there.

---

FailureCount #

failure-count

How many writes Excel refused

Signature:

Public Property Get FailureCount() As Long

A position the sheet does not have, or a sheet that stays protected, leaves the write undone. The count says how many, so a caller can tell a silent run from a run that did nothing.

---

WriteCount #

write-count

How many writes were issued since the bind or the last reset

Signature:

Public Property Get WriteCount() As Long

Every SetHidden, SetSize and SetOrientation counts one, refused or not. A caller about to save reads it beside ShowHide.HasChanges: nothing written and no choice moved means the store already holds what the sheet shows.

---

ResetWriteCount #

reset-write-count

Start the write count again, once a save has recorded the sheet

Signature:

Public Sub ResetWriteCount()

---

ForgetSizes #

forget-sizes

Drop every kept size

Signature:

Public Sub ForgetSizes()

For a caller whose sheet another layout instance may have resized in the meantime, such as the show/hide session after the layouts form has restored a saved layout. The next SizeWhenShown on a hidden position reads the sheet.

---

IsHidden #

is-hidden

Whether a position is hidden on the sheet

Signature:

Public Function IsHidden(ByVal position As Long) As Boolean

Reading and writing whether a position is hidden.

Parameters:

• position: Long. The column or row number.

Returns: Boolean. True when Excel is hiding it.

---

SetHidden #

set-hidden

Hide or show one position

Signature:

Public Sub SetHidden(ByVal position As Long, ByVal hidden As Boolean)

On the way to hiding, the size the position has is read first and kept on the instance, so SizeWhenShown can answer it later without showing the column again. Showing drops the kept size, because the sheet answers it.

Parameters:

• position: Long. The column or row number.
• hidden: Boolean. True to hide it.

---

Size #

size

The width or height of one position

Signature:

Public Function Size(ByVal position As Long) As Double

Reading and writing the width of a column or the height of a row.

Parameters:

• position: Long. The column or row number.

Returns: Double. The column width or the row height. A hidden position answers 0, which is how Excel reports it.

---

SetSize #

set-size

Set the width or height of one position

Signature:

Public Sub SetSize(ByVal position As Long, ByVal newSize As Double)

A size at or below zero is refused. Excel hides a column that is set to width 0, so writing a missing size would hide the very entry the caller is trying to restore.

Parameters:

• position: Long. The column or row number.
• newSize: Double. The width or height to write.

---

SizeWhenShown #

size-when-shown

The size a position would have if it were visible

Signature:

Public Function SizeWhenShown(ByVal position As Long) As Double

A hidden position reports a size of 0, so a migration file built from a live sheet used to record 0 for every hidden column and bring it back at width 0 - a column the user can neither see nor grab. A position this instance hid answers the size it read on the way to hiding it, with no write. One it found hidden already is revealed long enough to read its real size and hidden again; those two writes are not counted, since the sheet ends where it began.

Parameters:

• position: Long. The column or row number.

Returns: Double. The size the position holds when visible.

---

IsVertical #

is-vertical

Whether the printed header of one position is turned

Signature:

Public Function IsVertical(ByVal position As Long) As Boolean

The turned header of a printed sheet.

Excel stores a flat cell two ways: the angle 0, and the constant xlHorizontal (-4128), which is what a cell written with Orientation = 0 reads back as. Both mean flat, so both answer False here. Every flat header used to read as turned, because the test was angle <> 0.

Parameters:

• position: Long. The column number.

Returns: Boolean. True when the header cell reads bottom to top.

---

SetOrientation #

set-orientation

Turn the printed header of one position, or lay it flat

Signature:

Public Sub SetOrientation(ByVal position As Long, ByVal vertical As Boolean)

The row height and the column autofit belong to the turned branch alone. They used to run on every action, so choosing Hide on a column whose header was turned laid that header flat on the way to hiding it, and the direction was gone when the user showed the column again.

Parameters:

• position: Long. The column number.
• vertical: Boolean. True to turn the header.

---

BeginBatch #

begin-batch

Unprotect the sheet and keep it open

Signature:

Public Sub BeginBatch()

Holding the sheet open across a run of writes.

A loop that writes many positions calls this first, so the sheet is unprotected once instead of once per position. Every BeginBatch needs its EndBatch.

---

EndBatch #

end-batch

Close the bracket BeginBatch opened

Signature:

Public Sub EndBatch()

---