Sizes and Positions

The members that set the heights of the rows and the widths of the columns of a VBFlexGrid, let the user resize them, fit them to their contents, and report where cells and the client area are.

All sizes and positions here are in twips. The control keeps them in pixels, so a value that is read back can differ from the one assigned by the rounding to whole pixels.

A row or a column that has no size of its own has the default size. The default height of a row is the height of one line of the font plus a small spacing, and the default width of a column is four times that height. When FontFixed is set, the fixed rows and columns have the default size of that font. Setting a row height or a column width to -1 gives the row or the column the default size again.

The size of a row or a column is found in this order:

  1. A hidden row or column has the size 0.
  2. A row has the height that was set for it with RowHeight. When none was set, it has RowHeightUniform if that is not -1, and otherwise the default height. A column has the width set with ColWidth, or else the default width.
  3. A size that is not 0 is raised to RowHeightMin or ColWidthMin when it is smaller, and lowered to RowHeightMax or ColWidthMax when it is larger, when these are not 0.

RowHeight and ColWidth return the result of this order, not the number that was assigned. The last column can be widened to fill the control by ExtendLastCol, which changes what is drawn and not what ColWidth returns.

The user resizes a column by dragging the divider at its right edge in a fixed row, and a row by dragging the divider at its bottom edge in a fixed column, when AllowUserResizing permits. RowResizable and ColResizable exclude single rows and columns, and the events BeforeUserResize, AfterUserResize and AfterUserResizeEnd report the drag. A double click on a divider raises DividerDblClick; the grid does not resize anything then, but a handler can call AutoSize to fit the row or the column.

With VBFlexGrid1
    .Rows = 4
    .Cols = 3
    .TextMatrix(1, 1) = "ab"
    .TextMatrix(2, 1) = "a much longer piece of text"
    Dim before As Long
    before = .ColWidth(1)
    .AutoSize 1                                  ' fit column 1 to its longest text
    Debug.Print "Widened: " & (.ColWidth(1) > before)
    .RowHeight(2) = 900
    Debug.Print "Taller: " & (.RowHeight(2) > .RowHeight(1))
    .RowHeight(-1) = -1                          ' every row back to the default
    Debug.Print "Equal: " & (.RowHeight(2) = .RowHeight(1))
    .RowHidden(3) = True
    Debug.Print "Hidden: " & .RowHeight(3)
End With
' Output:
' Widened: True
' Taller: True
' Equal: True
' Hidden: 0

The position of a cell on the screen is given by RowPos and ColPos for any row and column, and by CellLeft, CellTop, CellWidth and CellHeight for the current cell. ClientLeft, ClientTop, ClientWidth and ClientHeight give the area inside the border and the scroll bars. The grid line of a cell lies inside the cell’s rectangle, on its right and bottom side; GetGridLineOffsets tells how much of the cell it takes. TextWidth and TextHeight measure a text in the font of a cell, and GetMetrics returns the fixed sizes that the control adds.

Properties

AllowUserResizing

Returns or sets whether the user can resize rows and columns with the mouse. A FlexAllowUserResizingConstants member. Default: FlexAllowUserResizingNone.

Syntax: object.AllowUserResizing [ = value ]

With FlexAllowUserResizingColumns, FlexAllowUserResizingRows or FlexAllowUserResizingBoth the user can drag the right edge of a column in a fixed row, and the bottom edge of a row in a fixed column. A row or column whose RowResizable or ColResizable is False cannot be dragged. A value that is not a member of the enumeration raises run-time error 380.

BestFitMode

Returns or sets which parts of a cell AutoSize and FormatString take into account for the best fit. A FlexBestFitModeConstants member. Default: FlexBestFitModeTextOnly.

Syntax: object.BestFitMode [ = value ]

With FlexBestFitModeTextOnly the best fit is the width of the text. The other modes add what shares the cell with the text:

Value Adds to the text
FlexBestFitModeFull the room of a combo button, of a check box beside the text and of a picture that does not overlap the text, and of a sort arrow
FlexBestFitModeSortArrowText the room of a sort arrow
FlexBestFitModeOtherText the room of a combo button, a check box and a picture, but not of a sort arrow

The sort arrow counts only in the fixed row named by RowSortArrows. The text of a cell that shows a picture from the column’s image list, or a check box in place of the text (FlexTextAsCheckBox), does not count. A column whose ColTextIndent is True gets the indentation from the CellTextIndent event added in every mode. A value that is not a member of the enumeration raises run-time error 380.

CellHeight

Returns the height of the current cell. A Long, in twips. Read-only.

Syntax: object.CellHeight

The height is that of the row less the grid line, so it is RowHeight of the current row less the top and bottom offsets of GetGridLineOffsets. Reading it first scrolls the grid so that the current cell is visible. When the grid has no current cell, that is when it has no rows or columns, it raises run-time error 30009 or 30010.

CellLeft

Returns the distance between the left edge of the control and the left edge of the current cell, in twips. A Long. Read-only.

Syntax: object.CellLeft

The distance is measured from the edge of the control, so the border of the control is included. Reading it first scrolls the grid so that the current cell is visible. The errors are those of CellHeight.

CellTop

Returns the distance between the top edge of the control and the top edge of the current cell, in twips. A Long. Read-only.

Syntax: object.CellTop

It is measured as CellLeft is, with the border included, and has the same side effect and the same errors. Together CellLeft and CellTop place a control, such as a text box or a combo box of your own, over the current cell.

CellWidth

Returns the width of the current cell. A Long, in twips. Read-only.

Syntax: object.CellWidth

The width is that of the column less the grid line: the result is ColWidth of the current column less the left and right offsets of GetGridLineOffsets. It has the same side effect and the same errors as CellHeight.

ClientHeight

Returns the height of the client area of the control, the part inside the border and the horizontal scroll bar. A Long, in twips. Read-only.

Syntax: object.ClientHeight

ClientLeft

Returns the distance between the left edge of the control and the left edge of its client area, in twips. A Long. Read-only.

Syntax: object.ClientLeft

The distance is the width of the border of the control on the left.

ClientTop

Returns the distance between the top edge of the control and the top edge of its client area, in twips. A Long. Read-only.

Syntax: object.ClientTop

ClientWidth

Returns the width of the client area of the control, the part inside the border and the vertical scroll bar. A Long, in twips. Read-only.

Syntax: object.ClientWidth

ColPos

Returns the distance between the left edge of the client area and the left edge of a column, in twips. A Long. Read-only.

Syntax: object.ColPos(Index)

Index
required A Long: the column. A column the grid does not have raises run-time error 30010.

The position is the one the column has now: a column that is scrolled out to the left of the first visible column has a negative position, and one that is scrolled out to the right a position beyond the client area. The fixed and frozen columns are always at the left.

ColResizable

Returns or sets whether the user can resize a column with the mouse. Boolean. Default: True.

Syntax: object.ColResizable(Index) [ = value ]

Index
required A Long: the column. For the assignment, -1 sets every column. A column the grid does not have raises run-time error 30010.

It has no effect on ColWidth and on the program, only on the mouse, and only when AllowUserResizing allows the resizing of columns.

ColWidth

Returns or sets the width of a column, in twips. A Long.

Syntax: object.ColWidth(Index) [ = value ]

Index
required A Long: the column. For the assignment, -1 sets every column. A column the grid does not have raises run-time error 30010.
value
A Long: the width, or -1 for the default width. A smaller value raises run-time error 30014 (Invalid Col Width value). 0 gives the column no width without hiding it: ColHidden stays False.

The result is the width that the column has, as the introduction of this page lists. It is 0 for a hidden column, it is limited by ColWidthMin and ColWidthMax, and it does not include what ExtendLastCol adds to the last column.

ColWidthMax

Returns or sets the largest width that a column has, in twips. A Long. Default: 0, which means no limit.

Syntax: object.ColWidthMax [ = value ]

A column that is wider is shown at this width, and a column whose width is 0 is not changed. A negative value raises run-time error 30014.

ColWidthMin

Returns or sets the smallest width that a column has, in twips. A Long. Default: 0, which means no limit.

Syntax: object.ColWidthMin [ = value ]

A column that is narrower is shown at this width, and a column whose width is 0 is not changed. A negative value raises run-time error 30014.

ExtendLastCol

Returns or sets whether the last column is widened to fill the client area. Boolean. Default: False.

Syntax: object.ExtendLastCol [ = value ]

When the columns together are narrower than the client area, the last column that is not hidden and has a width is drawn wider by the difference, so that no empty space is left on the right. The width that ColWidth returns for the column does not change, and the user cannot drag the divider on the right of the extended column.

FixGridLineOffsets

Returns or sets whether the grid line offsets follow the grid line style. Boolean. Default: False.

Syntax: object.FixGridLineOffsets [ = value ]

With False the offsets of every cell are those of the Microsoft FlexGrid control: 0 on the left and on the top and one pixel on the right and at the bottom, whatever GridLines is. That keeps the cells the same size and position as they have in that control. With True they follow the style of the cell’s grid lines: none for FlexGridLineNone, one pixel on the right and at the bottom for the flat, inset, dashed and dotted lines, one pixel on the left and on the top for the raised lines, and in the same way for the styles that draw horizontal or vertical lines only. The border at the end of the sheet (SheetBorder) and the lines of the frozen area add what they need.

RowHeight

Returns or sets the height of a row, in twips. A Long.

Syntax: object.RowHeight(Index) [ = value ]

Index
required A Long: the row. For the assignment, -1 sets every row. A row the grid does not have raises run-time error 30009.
value
A Long: the height, or -1 for the default height. A smaller value raises run-time error 30013 (Invalid Row Height value). 0 gives the row no height without hiding it: RowHidden stays False.

The result is the height that the row has, as the introduction of this page lists. It is 0 for a hidden row, and it is limited by RowHeightMin and RowHeightMax.

RowHeightMax

Returns or sets the largest height that a row has, in twips. A Long. Default: 0, which means no limit.

Syntax: object.RowHeightMax [ = value ]

A row that is taller is shown at this height, and a row whose height is 0 is not changed. A negative value raises run-time error 30013.

RowHeightMin

Returns or sets the smallest height that a row has, in twips. A Long. Default: 0, which means no limit.

Syntax: object.RowHeightMin [ = value ]

A row that is lower is shown at this height, and a row whose height is 0 is not changed. A negative value raises run-time error 30013.

RowHeightUniform

Returns or sets the height of all rows that have no height of their own, in twips. A Long. Default: -1, which means no uniform height.

Syntax: object.RowHeightUniform [ = value ]

A row whose height was set with RowHeight keeps it. Rows without one have this height instead of the default. With RowSizingMode set to FlexRowSizingModeUniform, resizing one row with the mouse sets this property and makes all the rows equal. A value below -1 raises run-time error 30013.

RowPos

Returns the distance between the top edge of the client area and the top edge of a row, in twips. A Long. Read-only.

Syntax: object.RowPos(Index)

Index
required A Long: the row. A row the grid does not have raises run-time error 30009.

The position is the one the row has now, as for ColPos: the fixed and frozen rows are always at the top, and a row scrolled out above the first visible row has a negative position.

RowResizable

Returns or sets whether the user can resize a row with the mouse. Boolean. Default: True.

Syntax: object.RowResizable(Index) [ = value ]

Index
required A Long: the row. For the assignment, -1 sets every row. A row the grid does not have raises run-time error 30009.

It has no effect on RowHeight and on the program, only on the mouse, and only when AllowUserResizing allows the resizing of rows.

RowSizingMode

Returns or sets what happens to the other rows when the user resizes one. A FlexRowSizingModeConstants member. Default: FlexRowSizingModeIndividual.

Syntax: object.RowSizingMode [ = value ]

Value The user resizes a row
FlexRowSizingModeIndividual and only that row changes its height
FlexRowSizingModeAll and every row gets that height
FlexRowSizingModeUniform and RowHeightUniform is set to that height, while the row itself goes back to using it

The property concerns the mouse only; assigning RowHeight works as always. A value that is not a member of the enumeration raises run-time error 380.

Methods

AutoSize

Sets the widths of columns or the heights of rows to fit their contents.

Syntax: object.AutoSize RowOrCol1 [ , RowOrCol2 [ , Mode [ , Scope [ , Equal [ , ExtraSpace [ , ExcludeHidden ] ] ] ] ] ]

RowOrCol1
required A Long: the first column (or row, for the mode FlexAutoSizeModeRowHeight) to fit.
RowOrCol2
optional A Long: the last column or row. The default, -1, is the same as RowOrCol1. A range can run backward. A value below -1 raises run-time error 380.
Mode
optional A FlexAutoSizeModeConstants member: FlexAutoSizeModeColWidth (the default) fits column widths, FlexAutoSizeModeRowHeight row heights.
Scope
optional A FlexAutoSizeScopeConstants member: the cells of each column (or row) that are measured. FlexAutoSizeScopeAll (the default) is all of them, FlexAutoSizeScopeFixed the fixed cells, FlexAutoSizeScopeScrollable the cells that are neither fixed nor frozen, FlexAutoSizeScopeMovable all cells that are not fixed, and FlexAutoSizeScopeFrozen the frozen ones.
Equal
optional A Boolean: True gives all the columns or rows the same size, that of the biggest. The default is False.
ExtraSpace
optional A Long: twips added to each size, on top of the cell spacing. The default is 0.
ExcludeHidden
optional A Boolean: True leaves hidden columns and rows out, both as the ones to fit and as the cells to measure. The default is False, so that a hidden row still counts.

A column or row that was fitted has the size of the biggest cell in its scope, but never less than the default size: the size of the column is first reset to the default, and then raised. The cell text is measured in the font of the cell, in what BestFitMode says, and as the cell displays it. AutoSize does nothing for a grid without rows or columns. A column or row that the grid does not have raises run-time error 381, and a mode or scope that is not a member of its enumeration raises 380.

With VBFlexGrid1
    .Rows = 3
    .Cols = 3
    .TextMatrix(1, 1) = "ab"
    .TextMatrix(2, 1) = "a much longer piece of text"
    .TextMatrix(1, 2) = "x"
    .AutoSize 1, 2, , , True                    ' columns 1 and 2, all of the same width
    Debug.Print .ColWidth(1) = .ColWidth(2)
    .RowHeight(1) = 3000
    .AutoSize 1, 2, FlexAutoSizeModeRowHeight   ' rows 1 and 2 fit their text
    Debug.Print .RowHeight(1) < 3000
End With
' Output:
' True
' True

GetGridLineOffsets

Returns how much of a cell’s rectangle its grid lines take on each side.

Syntax: object.GetGridLineOffsets Left, Top, Right, Bottom [ , Row [ , Col ] ]

Left, Top, Right, Bottom
required Long variables that receive the offsets in twips.
Row, Col
optional Long: the cell. -1 (the default) is the current row or column. A cell the grid does not have raises run-time error 381; a value below -1 raises 380.

The offsets depend on FixGridLineOffsets. With the default of False they are 0, 0, one pixel, one pixel for every cell. With True the last row and the last column have a pixel on the bottom and on the right in any case, for the line of SheetBorder. The cell’s content is drawn inside the rectangle less the offsets, as CellWidth and CellHeight show.

Dim l As Long, t As Long, r As Long, b As Long
With VBFlexGrid1
    .Rows = 3
    .Cols = 3
    .GetGridLineOffsets l, t, r, b
    Debug.Print l & " " & t & " " & (r > 0) & " " & (b > 0)
    .FixGridLineOffsets = True
    .GridLines = FlexGridLineRaised
    .GetGridLineOffsets l, t, r, b, 1, 1
    Debug.Print (l > 0) & " " & (t > 0) & " " & r & " " & b
    .GridLines = FlexGridLineNone
    .GetGridLineOffsets l, t, r, b, 1, 1
    Debug.Print l & " " & t & " " & r & " " & b
End With
' Output:
' 0 0 True True
' True True 0 0
' 0 0 0 0

GetMetrics

Returns one of the fixed sizes that the control uses.

Syntax: object.GetMetrics Metric, CX, CY

Metric
required A FlexMetricConstants member. Another value raises run-time error 380.
CX, CY
required Long variables that receive the width and the height, in twips.

The metrics are:

Metric Size
FlexMetricDividerSpacing the margin on each side of a divider in which the mouse pointer counts as being on the divider
FlexMetricTextPadding the space between the border of a cell and its text
FlexMetricCellSpacing the size that is added to the size of the text for the default size of rows and columns and for the best fit
FlexMetricScrollBarSize the width of the vertical and the height of the horizontal scroll bar
FlexMetricCheckBoxSize the size of a check box

The sizes scale with the display’s resolution.

TextHeight

Returns the height that a text needs in the font of a cell, in twips. A Long.

Syntax: object.TextHeight(Text [ , Row [ , Col ] ])

Text
required A String. Line breaks start new lines, and every line adds its height, unless SingleLine is True, which ignores them. An empty text gives the height of one line.
Row, Col
optional Long: the cell whose font is used, which can be a fixed cell, a cell with a font of its own, or one of the normal cells. -1 (the default) is the current row or column. A cell the grid does not have raises run-time error 381; a value below -1 raises 380.

TextWidth

Returns the width that a text needs in the font of a cell, in twips. A Long.

Syntax: object.TextWidth(Text [ , Row [ , Col ] ])

Text
required A String. For a text of several lines, the result is that of the widest line. An empty text has the width 0.
Row, Col
optional As for TextHeight.

The padding of the cell is not included: it is GetMetrics’s FlexMetricTextPadding on each side.

With VBFlexGrid1
    Debug.Print (.TextWidth("abcdefgh") > .TextWidth("ab")) & " " & .TextWidth("")
    Debug.Print .TextHeight("a" & vbCr & "b") > .TextHeight("a")
End With
' Output:
' True 0
' True

Events

AfterUserResize

Occurs when the user lets go of a divider after dragging it, before the new size is applied.

Syntax: object_AfterUserResize( Row As Long, Col As Long, NewSize As Long )

Row
The row that is resized, or -1 when a column is.
Col
The column that is resized, or -1 when a row is.
NewSize
The new height of the row or width of the column, in twips. It can be changed: the grid uses the value that the handler leaves. A value of 0 or less gives the row or column the size 0.

The size is always at least one pixel as the user drags it. Only a drag that moved the divider raises the event. After it the grid applies the size as RowSizingMode says for rows, and raises AfterUserResizeEnd.

Private Sub VBFlexGrid1_AfterUserResize(ByVal Row As Long, ByVal Col As Long, ByRef NewSize As Long)
    If Col > -1 And NewSize > 3000 Then NewSize = 3000   ' no column wider than 3000 twips
End Sub

AfterUserResizeEnd

Occurs after the grid has applied the size that the user gave a row or a column.

Syntax: object_AfterUserResizeEnd( Row As Long, Col As Long )

Row
The row that was resized, or -1 when a column was.
Col
The column that was resized, or -1 when a row was.

BeforeUserResize

Occurs when the user presses the mouse button on a divider of a row or a column, before the drag starts.

Syntax: object_BeforeUserResize( Row As Long, Col As Long, Cancel As Boolean )

Row
The row that would be resized, or -1 when it is a column.
Col
The column that would be resized, or -1 when it is a row.
Cancel
Set to True to stop the resizing. The default is False.
Private Sub VBFlexGrid1_BeforeUserResize(ByVal Row As Long, ByVal Col As Long, ByRef Cancel As Boolean)
    If Col = 0 Then Cancel = True   ' the first column keeps its width
End Sub

DividerDblClick

Occurs when the user double-clicks a divider of a row or a column.

Syntax: object_DividerDblClick( Row As Long, Col As Long )

Row
The row whose divider was clicked, or -1 when it is a column’s.
Col
The column whose divider was clicked, or -1 when it is a row’s.

The grid does nothing in response. The usual handler fits the row or the column to its contents:

Private Sub VBFlexGrid1_DividerDblClick(ByVal Row As Long, ByVal Col As Long)
    If Col > -1 Then
        VBFlexGrid1.AutoSize Col
    Else
        VBFlexGrid1.AutoSize Row, , FlexAutoSizeModeRowHeight
    End If
End Sub

See Also