Thread: How in VBA set Font Size for a checkbox?

I have some code that creates two groups of checkboxes on a radar chart (we call them "for tests" and "for series").
Can not figure out how to setup font.size for it. Tried to stick it in few spots - no luck.
Attached is a sample couple of checkboxes created by this code (for tests)

Any suggestions will be appreciated.

Public Sub AddControl(chartStar As Chart)
  Dim i As Long, j As Long
  Dim lcb As Single, tcb As Single, wcb As Single, hcb As Single, rng As Range, sha As Shape, cbOnOff As Boolean
  
  If NoData Then Exit Sub
  If chartStar Is Nothing Then Exit Sub
  
  With chartStar
' CheckBox for tests
    tcb = 4
    lcb = 4
    wcb = rngSource.Columns(0).Width + 12 ' 30
    hcb = (.ChartArea.Height - 8) / cntDataSeries ' 12
    If hcb > 16 Then hcb = 16
    If hcb < 12 Then hcb = 12
    
    For i = 1 To cntDataTest
      Set sha = .Shapes.AddFormControl(xlCheckBox, lcb, tcb, wcb, hcb)
      With sha
         .TextFrame.Characters.Text = rngSource(i, ncCheckBoxName - ncTestName).Text
        .Name = "cbT" & CStr(i)
        cbOnOff = (rngSource(i, ncChecked - ncTestName) <> "")
        If cbOnOff Then
          .ControlFormat.Value = xlOn
        Else
          .ControlFormat.Value = xlOff
          rngSource(i, 1).EntireRow.Hidden = True
          chartBar(i).Visible = xlSheetHidden
        End If
        .OnAction = "cbClick"
      End With

      tcb = tcb + hcb
      If tcb > .ChartArea.Height - hcb - 4 Then
        lcb = lcb + wcb + 1
        tcb = tcb - hcb * (cntDataTest - i)
      End If
    Next

' CheckBox for series
    tcb = 4
    lcb = .Legend.Left
    wcb = .Legend.Width
    hcb = 16
    For i = cntGradientPoint + 1 To cntGradientPoint + cntDataSeries
      Set sha = .Shapes.AddFormControl(xlCheckBox, lcb, tcb, wcb, hcb)
      With sha
        .TextFrame.Characters.Text = rngSource(0, i - cntGradientPoint).Text
        .Name = "cbS" & CStr(i - cntGradientPoint)
        .ControlFormat.Value = xlOn
        .OnAction = "cbClick"
        tcb = tcb + hcb
      End With
    Next

  End With
End Sub

Hi there,

The checkboxes your code is adding are similar to those which can be inserted using the "Forms" toolbar. As far as I know, the fontsize of these controls is fixed and cannot be altered either manually or via VBA.

If you want to specify the fontsize you must use an activex checkbox control (i.e. those which can be inserted using the "Control Toolbox" toolbar).

You'll need to use code something like the following:

    Dim chk As OLEObject

    Set chk = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
                                         DisplayAsIcon:=False, Left:=100, Top:=120, _
                                         Width:=140, Height:=30)
    chk.Object.Font.Size = 14

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thank you, Greg!

For now changing font on check boxes became lower priority, I might get back to it if users demand and will let you know.

Thanks again!

Hi again,

Many thanks for your feedback - I'm glad I was able to help.

Regards,

Greg M