Prettify dynamic grids

Using textboxes instead of grids.

Adding grids such as Flexgrid to VBA userforms is fraught with difficulties to do with versioning, security and so on. Sometimes you have to just bite the bullet and use a collection of Textboxes instead. However this leads to challenges for event handling and prettification. Event handling in dynamic forms is dealt with here.

Here are some solutions to creating the textboxes, and formatting them. There are a lot of code extracts embedded in this page. All are included in the Sudoku project which can be downloaded from here.

An array of textboxes

Let's say you have to create a set of textboxes where you don't know the details until run time, or indeed if there are so many that its boring to do it in the IDE, you can create them dynamically. In this case, in the Sudoku project creates a whole set of textboxes to act as a grid.

This is implemented through an instance of the cFormGrid class,  the textboxes are stored as an array of controls - ptbGrid(), and the event handlers are stored in the pControlEvents Collection.
Option Explicit
' this manages a grid on top of a user form
Private puForm As uiGrid
Private pPuzzle As cSudPuzzle

' Set constants
' Choose height and width of Label box
Const cTextBoxHeight As Long = 40

' Set required gap bewteen labels and gap from edge of form
Const cGap As Long = 0
Const cbGap As Long = 2
' set start pos of grid in form
Const cLeft As Long = 10
Const cTop As Long = 20
Const tbGrid = "tbGrid_"
Private ptbGrid() As Control
Private pSelectedIdx As Long
Private pControlEvents As Collection
Private pFilling As Boolean

This is constructed as below
Public Sub init(pu As UserForm, cp As cSudPuzzle)
    Set puForm = pu
    Set pPuzzle = cp
    ReDim ptbGrid(1 To pPuzzle.allcount)
    Set pControlEvents = New Collection
End Sub

The textboxes are constructed as follows
Public Sub createGrid()
    Dim idx As Long, ix As Long, iy As Long
    Dim iTop As Long, iLeft As Long, ptb As Control, plb As MSForms.Label
    Dim cHandler As cHandleControlEvents, sc As cSudCell, nCells As Long
    
    nCells = sGrid.cellCount
    
    ' first create a container label - strategically placed and colored this can fill in for lack of borders on TBs
    Set ptb = puForm.Controls.Add("forms.label.1", "label_grid_background")
        With ptb
            .Left = cLeft - cbGap
            .Top = cTop - cbGap
            .Height = (cTextBoxHeight + cGap) * nCells + numberofBoxesDown * (cbGap - cGap) + cbGap * 2
            .Width = .Height
        End With
    ' recast as  label
    Set plb = ptb
        With plb
            .BackColor = vbBlack
            .BorderColor = vbBlack
            .BorderStyle = fmBorderStyleSingle
            .SpecialEffect = fmSpecialEffectFlat
        End With
    
    ' now create a grid of textboxes
    iTop = cTop
    For ix = 1 To nCells
        iLeft = cLeft
        For iy = 1 To nCells
            
            ' index in grid = tb name and index
            idx = sGrid.RowIndex(ix, iy)
            
            ' create a textbox
            Set ptb = puForm.Controls.Add("forms.textbox.1", tbName(sGrid.Item(idx)))
            Set ptbGrid(idx) = ptb
            Set SelectedIdx = ptb
            
            ' create a handler for this object and add to collection
            Set cHandler = New cHandleControlEvents
            Set cHandler.Control = SelectedTb
            Set cHandler.FormGrid = Me
            pControlEvents.Add cHandler

            ' size and position it
            With ptb
                .Top = iTop
                .Left = iLeft
                .Height = cTextBoxHeight
                .Width = cTextBoxHeight
                iLeft = iLeft + cTextBoxHeight + boxGapHorizontal
            End With

            
        Next iy
        
        ' allow some space after each box to simulate a line
        If ix Mod sGrid.RowCount = 0 Then
            iTop = iTop + cTextBoxHeight + cbGap
        Else
            iTop = iTop + cTextBoxHeight + cGap
        End If
        
    Next ix
    
End Sub

This gives us a grid that looks like this. 

Identifying the textboxes

Since each instance of cHandleControlEvents has its own set of event handlers using common code, we would need to be able to identify which textbox is generating the event. In this case the textboxes have been named  such that they contain their index in the control array, and the names or index are extracted or set using these properties of cFormGrid.
Const tbGrid = "tbGrid_"
Private Property Get IdxFromTbName(p As String) As Long
    IdxFromTbName = Right(p, Len(p) - Len(tbGrid))
End Property
Private Property Get tbNumber(sc As cSudCell) As Long
    tbNumber = sc.Index
End Property
Private Property Get tbName(sc As cSudCell) As String
    tbName = tbGrid & Format(tbNumber(sc), "0000")
End Property

Later when an event is triggered, in this case the keypress method of the cHandleControlEvents class,  it calls the gotentry method of the appropriate cFormGrid instance to process the key received, after having changed the focus to the new cell.
Private Sub ptb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim s  As String
    newFocus
    With pFormGrid
        If IsNumeric(Chr(KeyAscii)) Then
            .gotEntry (CLng(Chr(KeyAscii)))
        ElseIf KeyAscii = 32 Then ' space
            .gotEntry (0)
        
        End If
    End With
    
End Sub
Private Sub newFocus()
    ' we will highlight the tb as it gets selected
    Application.EnableEvents = False
    With pFormGrid
        .lostFocus
        Set .SelectedIdx = Control
        .gotFocus
    End With
    Application.EnableEvents = True
End Sub
Public Property Get Control() As Control
    Set Control = ptb
End Property

.. in cFormGrid
Public Sub gotEntry(kp As Long)
    ' we have a keyboard entry
    Dim dirty As Boolean, sc As cSudCell
    dirty = False
    
    With SelectedsCell
        If kp >= 1 And kp <= pPuzzle.cellCount Then
            'this is an attempt to change the contents of a cell
            dirty = .isPossibility(kp)
            If Not dirty Then
                dirty = (MsgBox(CStr(kp) & " is not a valid possibility - are you sure you want to insert it ?", vbYesNo) = vbYes)
            End If
            If dirty Then
                 .clearScrewedUp
                .makeSure kp, False
                If isEnterAsGivens Then
                    .makeGiven
                End If
            
            End If
        ElseIf kp = 0 Then
            .clearSure
            .clearScrewedUp
            dirty = True

        End If
        
    End With
    If dirty Then
        Set sc = SelectedsCell
        pPuzzle.Grid.resetPossibilities
        Fill
        pSelectedIdx = sc.Index
    End If
End Sub

Recasting controls and textboxes

One thing that is a nuisance when dealing with controls, is that the properties associated with a control object are not the same as with a textbox object, so depending on what you want to do, you need to recast. A simple way to get round this is to create a property that recasts to the appropriate object. Using the concept of a 'selected textbox', these cFormGrid properties return a textbox, a control, or a cSudCell as required, based on the index of the currently selected textbox. 
Public Property Set SelectedIdx(p As Control)
    pSelectedIdx = IdxFromTbName(p.Name)
    Debug.Assert ptbGrid(pSelectedIdx).Name = p.Name
End Property
Public Property Get SelectedsCell() As cSudCell
    Debug.Assert pSelectedIdx <> 0
    Set SelectedsCell = pPuzzle.Grid.Item(pSelectedIdx)
    
End Property
Public Property Get SelectedControl() As Control
    Set SelectedControl = ptbGrid(pSelectedIdx)
    
End Property
Public Property Get SelectedTb() As MSForms.TextBox
    Set SelectedTb = SelectedControl
    
End Property


Dealing with borders

One problem with textboxes is the lack of formatting options for borders. In the case of a Sudoku grid, you need to be able to have different border thicknesses depending on where a textbox is on the grid.

After some headscratching, I realized that if I created a label control slightly bigger than the total size of all the textboxes, set the background color to black, then laid the textbox array on top of it, leaving a gap between those that needed a thick border, i could simulate the border effects I needed. 

Creating a background label slightly bigger than the textbox grid
    ' first create a container label - strategically placed and colored this can fill in for lack of borders on TBs
    Set ptb = puForm.Controls.Add("forms.label.1", "label_grid_background")
        With ptb
            .Left = cLeft - cbGap
            .Top = cTop - cbGap
            .Height = (cTextBoxHeight + cGap) * nCells + numberofBoxesDown * (cbGap - cGap) + cbGap * 2
            .Width = .Height
        End With
    ' recast as  label
    Set plb = ptb
        With plb
            .BackColor = vbBlack
            .BorderColor = vbBlack
            .BorderStyle = fmBorderStyleSingle
            .SpecialEffect = fmSpecialEffectFlat
        End With

Variable sized fonts

Sometimes you need to vary the font size depending on the size of the textbox. For that you need to know the relationship between points (for font size) and pixels (for textbox size). 

These properties of cFormGrid do the conversion.
Private Function PointsToPixels(npoints) As Long
    PointsToPixels = 24 / 18 * npoints
End Function
Private Function PixelsToPoints(npixels) As Long
    PixelsToPoints = 18 / 24 * npixels
End Function
Private Function pixelsToInches(npixels) As Long
    pixelsToInches = 0.25 / 24 * npixels
End Function

and are used here to vary the font size to the textbox size
Public Sub fillTb()
    Dim s As String, ip As Long, sc As cSudCell
    
   Application.EnableEvents = False
   pFilling = True
    Set sc = SelectedsCell
    clearTb
    With SelectedTb
        If sc.isSure Then
            .Font.Size = PixelsToPoints(SelectedControl.Width)
            .Text = sc.Value
        Else
            If isPencilMark Then
                .Font.Size = PixelsToPoints(SelectedControl.Width / Sqr(pPuzzle.cellCount))
                s = ""
                For ip = 1 To pPuzzle.cellCount
                    If sc.isPossibility(ip) Then
                        s = s & CStr(ip) & " "
                    End If
                Next ip
                If Len(s) > 0 Then
                    .Text = Trim(s)
                End If
            End If
        End If
    End With
    pFilling = False
    Application.EnableEvents = True
End Sub

That gives us this result , showing multiple font sizes based on size of textbox