CODEWITHSUNDEEP
excelvbacheckbox
SPlatten
SPlatten

Reputation: 5762

Excel 2003, adding content with checkboxes

I am propulating a range with rows using VBA, each row will have its own checkbox.

So far the code looks like this:

    Dim objColumnHeadings As Range, objDBsheet As Worksheet
    Dim lngRow As Long, objCell As Range
    Dim objCheckbox As Object
    Set objDBsheet = getDBsheet()
    Set objColumnHeadings = objDBsheet.Range("ColumnHeadings")
    objColumnHeadings.ClearContents
    lngRow = 1
    For Each varExisting In objColumns
        objColumnHeadings.Cells(lngRow, 1).Value = varExisting
        Set objCell = objColumnHeadings.Cells(lngRow, 2)
        Set objCheckbox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1" _
                            , Left:=412.8 _
                            , Top:=objCell.Top _
                            , Height:=10 _
                            , Width:=9.6)
        objCheckbox.Name = "cb" & lngRow
        objCheckbox.Appearance.Caption = ""
        objCheckbox.Appearance.BackColor = &H808080
        objCheckbox.Appearance.BackStyle = 0
        lngRow = lngRow + 1
        If lngRow > 1 Then
            Exit For
        End If
    Next

Setting the name of the checkbox works, but setting the other properties does not and results in a Run-time error: '438', Object doesn't support this property or method.

When I look at the properties of the newly added checkboxes the Name is set correctly, but Caption, BackColor and BackStyle are not set.

How do I set these programatically?

Upvotes: 1

Views: 120

Answers (1)

cyboashu
cyboashu

Reputation: 10443

Use MSForms.CheckBox, and the set Object in it, that's easier. Use the following example code.

Sub test()

    Dim objCheckbox As MSForms.CheckBox

     Set objCheckbox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1" _
                            , Left:=10.8 _
                            , Top:=10 _
                            , Height:=25 _
                            , Width:=200).Object
        objCheckbox.Name = "Dummy_Test"
        objCheckbox.Caption = "Test"
        objCheckbox.BackColor = vbRed
        objCheckbox.BackStyle = 0

End Sub

Upvotes: 1

Related Questions