Check Box (Form Control) box sizing and font sizing?

TinaHaHRCA 

I ran into the same probem... It took me a while but I was able to figure it out!

I have a dictionary with all the names of the connections that retrieve data from our system using PowerQuery into tables. The dictionary contains the name of each connection and a description of the connection. I wanted to show the progress of the refreshing process per connection in a dedicated form called "frm_refresh" by making a checkbox per connection. When the form is shown, all checkbox are disabledabled,greying them out.  Instead of showing the name of the query (which is not really informative for the user), I changed the caption of each checkbox to the description of the connection from the dictionary.

When initializing the form with the UserForm_Initialize() event, I was able to dynamically add the number of checkboxes depending on the number of elements in the dictionary. When adding the checkboxes I was able to set basic properties as follows:

Dim conn As Variant
Dim actTop As Long: actTop = 120               ' .Top value for first checkbox

For Each conn In glb_conns
    Dim chb As New Control
    Set chb = Me.Controls.Add("Forms.Checkbox.1")
    With chb
        chb.Name = conn                            ' Name of connection
        chb.Caption = glb_conn_descs(conn)("desc") ' Description of connection
        chb.Top = actTop                           ' Top of checkbox
        chb.Left = glb_conns_frm_checkboxLeft      ' Left from global value
        chb.Height = glb_conns_frm_checkboxHeight  ' Height from global value 
        chb.Width = glb_conns_frm_checkboxWidth    ' Width from global value
    End With
    actTop = actTop + 16                           ' Increase .Top value
Next

But I couldn't set properties like chb.Font, chb.Font.Size or chb.Font.Bold

So I used the USerForm_Activate() event (which is triggered when the form is first shown) to set the wanted properties as follows:

Dim conn As WorkbookConnection
For Each conn In ActiveWorkbook.Connections
    Me.Controls(conn.Name).Enabled = True
    Me.Controls(conn.Name).Font.Size = 9
    Me.Controls(conn.Name).Font.Bold = False
    Me.Controls(conn.Name).Value = False
    Me.Controls(conn.Name).Enabled = False
Next
DoEvents

Now my form was ready for use and all checkboxes were shown as I wanted them to. Then all the user needs to do is to click the button 'Start refreshing'. Then loop through all connections, set BackgroundQuery to False so VBA waits for the completion of each refreshing and depending on the result of the refresh, I could modify my checkboxes accordingly:

Private Sub btn_start_Click()
    ' Button 'Start refreshing' was clicked  
    
    Dim conn As WorkbookConnection    
    For Each conn In ActiveWorkbook.Connections

        ' Enable checkbox to make it active and black instead of greyed
        ' so the user can see which connection is being refreshed
        Me.Controls(conn.Name).Enabled = True
        
        ' Disable .BackgroundQuery so execution halts for completion of query
        conn.OLEDBConnection.BackgroundQuery = False
        
        ' Refresh the connection
        On Error GoTo ErrorHandler
        conn.Refresh
        
        ' Refreshing is completed without errors
        Me.Controls(conn.Name).Value = True                  ' Check as completed
        Me.Controls(conn.Name).ForeColor = glb_color_green   ' Green for OK
        Me.Controls(conn.Name).Font.Bold = True
        
ErrorResume:
    Next conn

    Exit Sub
        
ErrorHandler:
    If Err.Number <> 0 Then
        ' An error occured during refreshing the connection
        Me.Controls(conn.Name).Value = False              ' Uncheck (not completed)
        Me.Controls(conn.Name).ForeColor = glb_color_red  ' Red for error
        Me.Controls(conn.Name).Font.Bold = True           ' Bold font
    End If
    Resume ErrorResume
        
End Sub

Summarizing: You can't dynamically set font-related properties when creating them. But you can set them afterwards when the form is loaded and shown.

Hope this helps!