Macro to Add a CheckBox (XL2K)

Topic

New Reply

I need a macro to add a checkbox on a worksheet. So far I have

Option Explicit

Sub test()
' This macro places a checkbox in the selected cell
Dim cb As OLEObject
Dim c As MSForms.CheckBox
    With ActiveCell
        .RowHeight = 21
        Set cb = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                    Left:=.Left + 1, Top:=.Top + 1, Width:=1000, Height:=19.5)
        cb.Name = "chkTest"
        cb.Object.Caption = "Pretty Cool"
        cb.Object.AutoSize = True
        cb.LinkedCell = .Address
        cb.Object.Value = False
        .Offset(2, 2).Select
    End With
End Sub

This works, but the Name, chkTest, is associated with the OLEObject, ie it appears in the Name Box when the control is selected in the design mode. What I wanted was the actual control name to be chkTest. That way I could pre-load or dynamically create a click event. I don’t think that this will be necessary, but it has me puzzled. The same code works fine for a ClassType:=”Forms.CommandButton.1″. Then the Name is assigned to the control. Weird! TIA –Sam

Reply | Quote

Replies

It is much simpler to add checkboxes from the forms toolbar:

Sub AddChecks()
Dim dRowheight As Double
Dim lCount As Long
Dim oCheckbox As Object
    dRowheight = ActiveSheet.Rows(1).Height
    For lCount = 1 To 10
        Set oCheckbox = ActiveSheet.CheckBoxes.Add(Top:=(lCount - 1) * dRowheight _
, Left:=10, Height:=dRowheight, Width:=60)
        With oCheckbox
            .Caption = "Box " & lCount
            .Name = "Checkbox_" & lCount
            .OnAction = "CheckBoxes_Onclick"
        End With
    Next
End Sub
Sub CheckBoxes_Onclick()
    Dim sCaller As String
    sCaller = Application.Caller
    If ActiveSheet.CheckBoxes(sCaller).Value  -4146 Then
        MsgBox sCaller & " is checked."
    Else
        MsgBox sCaller & " is not checked."
    End If
End Sub

If you still wish to use checkboxes from the control toolbox, I would create a class module that handles the click event of each checkbox. Then all you need to do is instantiate the event handler for the checkboxes.

The coding involved is rather tricky and too complicatied to describe here. It is well described in some VBA books though.

Reply | Quote

I fought Excel until I couldn’t stand it, then I rewrote it your way. It works perfectly, plus it saved me time in the long run when I needed them all to call the same macro. Thanks, Jan Karel!

BTW, not that it matters, but being old, I wanted to increase the font size of the checkbox text. Can it be done? I couldn’t find any such property. Also, can you figure out any good way to “autosize” the width of the checkbox? I load the text in at run-time and set the forecolor to light green to get the user’s attention. Only thing that I could figure out to do was write the captions into an unused column on the worksheet, do an autofit, and use the resulting column width plus the width of the “check.” Thanks! –Sam

Reply | Quote

There is no way to adjust the size of the text in the FORMS toolbar (other than playing with the zoom and the fontsize of the sheet).

The “ease of use” of the FORMS toolbar items comes at the price that there is not a lot of customization. The CONTROL Toolbar items are more difficult to use, but can be customized.
Steve

Reply | Quote

Could you please explain how to size the box using the control toolbar? I have just tried very briefly and nothing stands out to me as being very useful. A situation just came up with a form we created where the client is telling us that the check boxes are “too small.” We would like to maintain the ability to automatically check the boxes, but can’t figure out how to increase the size of the boxes. Thanks.

Reply | Quote

I don’t think the size of a check box can be changed. Take a look at Pieterse’s reply to a similar question in post 226630.

Reply | Quote

There is a slight confusion over the meaning of box here. I was referring to the entire control as a box. You can resize this easily, but it does not affect the size of the font nor the size of the checkbox-box. In my case, I fill everything in light green that requires user input, so I wanted all of the checkbox controls to have the same width. You might want to just increase the size of the control, since clicking anywhere on the control causes the box to be checked. If it is a forms checkbox, then you could right-click and have a border line and/or fill and explain to the user that you can click anwhere on the control. If you have a VBA checkbox, then you enter the design mode and change the properties. With this control, you can increase the font size, but not the little checkbox-box. HTH –Sam

Reply | Quote

The actual box part of a check box cannot be resized, regardless of which type you are using.

See this thread, for a possible alternative.

Andrew C

Reply | Quote

I guess your workaround to autofit is as good as any. No other way to do this than by trial and error.

Steve said it all: Forms controls are simpler to use and hence limited in their formatting options.

Reply | Quote

Sammy ,

I’m not sure what problem you are having, but your code works fine for me. It creates a CheckBox named chkTest. Altering it to create a CommandButton also works in the same way. The following piece of code should add a Click event to the chkText checkbox :

Dim shtModule
Dim strOnlick As String
Set shtModule = ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
strOnClick = “Private Sub chkTest_Click” & _
vbCrLf & vbTab & “MsgBox ” & Chr(34) _
& “Testing chkTest ” & Chr(34) & vbCrLf & “End Sub”
With shtModule
.InsertLines .CountOfLines + 1, strCode
End With
Set shtModule = Nothing

Also see the attached capture of the properties for both a Commandbutton and Checkbox created with your method.

Andrew

Reply | Quote