VBA FlexGrid control coding for fun

FlexGrid .cls

Option Explicit
Option Base 1

Private WithEvents flx As MSFlexGrid
Private strFlexGridName As String
Private frmParent As UserForm
Private frmChild As UserForm
Private enItemClick As OnItemClick

Private strToOpenOnClick As String

Private Enum OnItemClick
    xlOpenSubForm = 1
    xlEditItem = 2
End Enum


Private varFlexGridHeaders As Variant    'two dimension array that stores column header names and widths
Private varFlexGridData_Initial As Variant      'array that stors main recordset
Private varFlexGridData_Updated As Variant      'array that stors main recordset

Private Const PIX_TO_TWIPS As Single = 1440 / 72
Private m_EditRow As Integer
Private m_EditCol As Integer

Private Sub Class_Initialize()

Set flx = New MSFlexGrid
enItemClick = xlEditItem 'by default edit item on click, alternative option is to display Child userform

End Sub
Private Sub Class_Terminate()

Set flx = Nothing

End Sub
Property Let FlexGridName(strName As String)

strFlexGridName = strName

End Property
Property Let ParentForm(frmUserForm As UserForm)

Set frmParent = frmUserForm

End Property
Property Let ChildForm(frmUserForm As UserForm)

Set frmChild = frmUserForm

End Property
Property Let FlexGridHeaders(varHeaders As Variant)

If IsArray(varHeaders) Then
    varFlexGridHeaders = varHeaders
Else
    MsgBox "Variable varHeaders is not an array!"
    Exit Property
End If

End Property
Property Let FlexGridData(varData As Variant)

If IsArray(varData) Then
    varFlexGridData_Initial = varData
Else
    MsgBox "Variable varData is not an array!"
    Exit Property
End If

End Property
Sub SetFlexGrid()

If strFlexGridName = "" Then

    MsgBox "The name of FlexGrid control was not provided."
    Exit Sub
    
Else
    
    Set flx = frmParent.Controls(strFlexGridName)
    
    With flx
    
        .FixedCols = 0
        .FixedRows = 1      'fixed header row
    
        Me.UpdateFlexGrid varFlexGridHeaders, varFlexGridData_Initial
               
    End With
    
End If

End Sub
Sub UpdateFlexGrid(varHeaders As Variant, varData As Variant)

'this procedure is designed to work with 1 to x arrays

Dim i As Long
Dim j As Long
Dim c As Integer
Dim r As Long
Dim bytCorrectionR As Byte
Dim bytCorrectionC As Byte
Dim wid
Dim varMerged As Variant

'check if flx was created
If flx Is Nothing Then
    MsgBox "Variable flx is nothing. FlexGrid object cannot be found!"
    Exit Sub
End If

'merge header and data array
If UBound(varData, 2) <> UBound(varHeaders, 2) Then
    MsgBox "Arrays varData and varHeaders have different dimensions! Unable to create merged dataset!"
    Exit Sub
End If

'fill in temporary array
ReDim varMerged(LBound(varData, 1) To UBound(varData, 1) + 1, LBound(varData, 2) To UBound(varData, 2))
For i = LBound(varHeaders, 2) To UBound(varHeaders, 2)
    varMerged(1, i) = varHeaders(1, i)
Next i
For i = LBound(varData, 1) To UBound(varData, 1)    'for each record
    For j = LBound(varData, 2) To UBound(varData, 2)
        varMerged(i + 1, j) = varData(i, j)
    Next j
Next i

With flx

    .Cols = UBound(varMerged, 2)
    .Rows = UBound(varMerged, 1)
    wid = ((.Width - 8) * PIX_TO_TWIPS - 300) / 4
    
    For c = 1 To .Cols
        .TextMatrix(0, c - 1) = varMerged(1, c)
        .ColWidth(c - 1) = wid
    Next c
    
    For r = 2 To .Rows
        For c = 1 To .Cols
            .TextMatrix(r - 1, c - 1) = varMerged(r, c)
        Next c
    Next r
    
End With

End Sub
Private Sub flx_Click()

Debug.Print "hello!"

End Sub