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

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