Add Command Buttons to User Form at Runtime

I've made some changes to the code.

• Allow different button name to macro name (array is made up of pairs, 1st=button name, 2nd=macro name)
• Delete Form after close (Thanks Norie)
• Unload Form before running sub
• Button width set by len of largest button name string
• Attempted to calc a more pleasing ratio of columns to rows (THIS STILL WIP )

Updated Module below:

Option Explicit
Option Private Module

Private objFrm As Object

Private Const bytMarginW    As Byte = 5
Private Const bytMarginH    As Byte = 7
Private Const strFormName   As String = "UserForm1" 'keep the temp form on this name
Rem DON'T RENAME! After corrupting several workbooks beyond repair, don't recommend renaming a form while adding it to workbook

Public Sub CreateTempMenuForm(ByRef varMacroArray As Variant, Optional ByVal strFormCaption As String)
'\ Creates a Temporary Menu form for calling various macros. Form should be deleted on exit.


'   delete Temp Menu UserForm (In case wasn't deleted last time it was run)
    Call KillTheTemporaryMenuForm(strFormName)

'   set object as userform
    Set objFrm = ThisWorkbook.VBProject.VBComponents.Add(3) 'vbext_ct_MSForm

'   set up form controls & code
    Call FormOther
    Call FormButtons(varMacroArray)
Rem Would like to have the buttons placed in a visually pleasing ratio of rows & columns.

'   add object to Forms collection & show
    With VBA.UserForms.Add(objFrm.Name)
        If Len(strFormCaption) > 0 Then
            .Caption = strFormCaption
        Else
            .Caption = "MENU" 'add generic title if not provided by user
        End If
        .Show
    End With
Rem When a new UserForm is created, it exists in VBA's memory however it isn't officially part of the project yet. _
So you need to use the Add method to formally enroll UserForm1 into the collection of UserForms before it can be used as a form.

'   remove form after closing
    ThisWorkbook.VBProject.VBComponents.Remove objFrm
End Sub

Public Sub KillTheTemporaryMenuForm(ByRef strTempForm As String)
Dim vbComp As Object

    For Each vbComp In ThisWorkbook.VBProject.VBComponents
        If vbComp.Type = 3 Then
            If vbComp.Name = strTempForm Then
                Debug.Print vbNewLine & Now() & ": About to remove Form: " & vbComp.Name
                ThisWorkbook.VBProject.VBComponents.Remove vbComp
                Exit For
            End If
        End If
    Next vbComp
End Sub

Private Sub FormOther()
Dim lngLines As Long

    With objFrm.CodeModule
    'Activate (sets form position)
        lngLines = .CountOfLines
        .InsertLines lngLines + 1, "Sub UserForm_Activate"
        .InsertLines lngLines + 2, vbTab & "With Me"
        .InsertLines lngLines + 3, vbTab & vbTab & ".StartUpPosition = 0"
        .InsertLines lngLines + 4, vbTab & vbTab & ".Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)"
        .InsertLines lngLines + 5, vbTab & vbTab & ".Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)"
        .InsertLines lngLines + 6, vbTab & "End With"
        .InsertLines lngLines + 7, "End Sub"
    'QueryClose (kill form on close)
        lngLines = .CountOfLines + 1
        .InsertLines lngLines + 1, "Private Sub UserForm_QueryClose(ByRef Cancel As Integer, ByRef CloseMode As Integer)"
        .InsertLines lngLines + 2, vbTab & "If CloseMode = vbFormControlMenu Then Call KillTheTemporaryMenuForm(Me.Name)"
        .InsertLines lngLines + 3, "End Sub"
    End With
End Sub

Private Sub FormButtons(ByRef varMacroArray As Variant)
Dim bytMaxRow       As Byte
Dim bytMaxCol       As Byte
Dim lngBtnW         As Long
Dim bytCountButtons As Byte
Dim bytLoopBtn      As Byte
Dim bytLoopCol      As Byte
Dim Btn             As MSForms.CommandButton
Dim lngTop          As Long
Dim lngLeft         As Long
Dim lngLines        As Long
Dim avarNamesButtons    As Variant
Dim avarNamesSubs       As Variant

'   create two sub arrays from input array
    avarNamesSubs = Extract1DArrayOfElementsFrom1DArray(varMacroArray, True)
    avarNamesButtons = Extract1DArrayOfElementsFrom1DArray(varMacroArray, False)
    Erase varMacroArray

'   for grid of buttons - count buttons needed + calc max rows & max cols needed
    Call SetMaxRowsAndCols(avarNamesButtons, bytMaxRow, bytMaxCol, bytCountButtons)

'   set button width
    lngBtnW = ReturnLongestLenFrom1DArrElement(avarNamesButtons)
    lngBtnW = lngBtnW * 4.2

'   loop to add buttons
    lngLeft = bytMarginW
    lngTop = bytMarginH
    bytLoopCol = 0

    For bytLoopBtn = 1 To bytCountButtons
        'add new button - position
        Set Btn = objFrm.Designer.Controls.Add("forms.CommandButton.1")
        With Btn
            .Left = lngLeft
            .Top = lngTop
            .Width = lngBtnW
            If Not bytLoopBtn = bytCountButtons Then
                .Caption = avarNamesButtons(bytLoopBtn)
                '.WordWrap = True
            Else 'last button is exit button
                .Caption = "Exit"
                .Accelerator = "X"
            End If
        End With
        'add new button - code
        With objFrm.CodeModule
            lngLines = .CountOfLines + 1
            .InsertLines lngLines + 1, "Sub CommandButton" & bytLoopBtn & "_Click()"
            If Not bytLoopBtn = bytCountButtons Then
                .InsertLines lngLines + 2, vbTab & "Unload Me" 'don't want form showing OR loaded while sub is running
                .InsertLines lngLines + 3, vbTab & "Call " & avarNamesSubs(bytLoopBtn)
                '.InsertLines lngLines + 4, vbTab & "Call KillTheTemporaryMenuForm(Me.Name)" 'want Temp Form deleted after sub runs
                .InsertLines lngLines + 5, "End Sub"
                '.InsertLines lngLines + 2, vbTab & "Call KillTempMenuBeforeRunningSub(Me.Name," & Chr(34) & avarNamesSubs(bytLoopBtn) & Chr(34) & ")"
            Else 'last button is exit button
                .InsertLines lngLines + 2, vbTab & "Call KillTheTemporaryMenuForm(Me.Name)"
                .InsertLines lngLines + 3, "End Sub"
                Exit For
            End If
        End With
        'add new button - completed loop
        bytLoopCol = bytLoopCol + 1
        'set position next button
        If bytLoopCol = bytMaxCol Then
            'start next row
            bytLoopCol = 0
            lngLeft = bytMarginW
            lngTop = lngTop + bytMarginH + Btn.Height
        Else 'start next col
            lngLeft = lngLeft + bytMarginW + lngBtnW
        End If
    Next bytLoopBtn

'   remove
    Erase avarNamesButtons
    Erase avarNamesSubs
    bytLoopBtn = vbNull
    bytLoopCol = vbNull
    lngTop = vbNull
    lngLeft = vbNull
    lngLines = vbNull
    bytCountButtons = vbNull

'   set Form dimensions
    With objFrm
        .Properties("Height") = (Btn.Height + bytMarginH) * (bytMaxRow + 1) - bytMarginH
        .Properties("Width") = (lngBtnW + bytMarginW) * (bytMaxCol) + (bytMarginW * 2)
    End With
End Sub

Private Sub SetMaxRowsAndCols(ByRef varMacroArray As Variant, _
                                ByRef bytMaxRow As Byte, _
                                ByRef bytMaxCol As Byte, _
                                ByRef bytCountButtons As Byte)

Dim bytTest As Byte

'   Count no. of buttons required
    bytCountButtons = UBound(varMacroArray) - LBound(varMacroArray) + 1 '+1 to make result inclusive

'   add 1 more button (for the Exit button)
    bytCountButtons = bytCountButtons + 1

'   determine how many rows/columns to use

'   use preset layouts for low number of buttons
    Select Case bytCountButtons
        Case Is < 5: 'i x 1
            bytMaxRow = bytCountButtons
            bytMaxCol = 1
        Case 5 To 6:
            bytMaxRow = 3
            bytMaxCol = 2
        Case 7 To 8:
            bytMaxRow = 4
            bytMaxCol = 2
        Case 9:
            bytMaxRow = 3
            bytMaxCol = 3
        Case 10: '2 x 5
            bytMaxRow = 5
            bytMaxCol = 2
        Case 11 To 12:
            bytMaxRow = 4
            bytMaxCol = 3
        Case 13 To 15:
            bytMaxRow = 5
            bytMaxCol = 3
        Case 16
            bytMaxRow = 4
            bytMaxCol = 4
        Case 17 To 18
            bytMaxRow = 6
            bytMaxCol = 3
        Case Else
        End Select

    If bytMaxCol > 0 Then Exit Sub

'   somehow work out a formula to pick a pleasing ratio of rows to columns...
    bytTest = bytCountButtons

    Do Until bytMaxCol > 0
        If bytTest Mod 5 = 0 Then
            bytMaxCol = 5
        ElseIf bytTest Mod 7 = 0 Then bytMaxCol = 7
        ElseIf bytTest Mod 9 = 0 Then bytMaxCol = 9
        End If
        bytTest = bytTest + 1
    Loop

Solved:
    'work out rows from no. of columns chosen
    bytMaxRow = bytCountButtons / bytMaxCol
End Sub