change the text in a checkbox

[Weaver]

I'm building multiple checkboxes into cells on the fly. How the heck to you change the caption?

I've commented out two of the several attempts I've made

Sub insertCheckboxes()
    For x = 1 To 10
        Set c = Cells(x, "A")
        n = "Choice_" & Format(x, "00")
        With ActiveSheet.OLEObjects.Add( _
            ClassType:="Forms.CheckBox.1", _
            Link:=False, _
            DisplayAsIcon:=False, _
            Left:=c.Left, _
            Top:=c.Top, _
            Width:=c.Width, _
            Height:=c.Height)
            .Name = n
            .LinkedCell = c.Address
            [COLOR=red]'.Caption = n[/COLOR]
        End With
        With ActiveSheet.Shapes(n)
            .Placement = xlMoveAndSize
            [COLOR=red]'.Caption = n[/COLOR]
[COLOR=black]        End With[/COLOR]        
        c.Value = False
    Next x
End Sub

 

[RoryA]

You need to refer to the object itself:

Sub insertCheckboxes()
    For x = 1 To 10
        Set c = Cells(x, "A")
        n = "Choice_" & Format(x, "00")
        With ActiveSheet.OLEObjects.Add( _
            ClassType:="Forms.CheckBox.1", _
            Link:=False, _
            DisplayAsIcon:=False, _
            Left:=c.Left, _
            Top:=c.Top, _
            Width:=c.Width, _
            Height:=c.Height)
            .Name = n
            .LinkedCell = c.Address
            .Object.Caption = n
        End With
        ActiveSheet.Shapes(n).Placement = xlMoveAndSize
        c.Value = False
    Next x
End Sub

 

[Weaver]

Cheers - that really had me scratching my head.

 

[RoryA]

The joys of ActiveX and its containers.