VBA to add checkboxes 2003 vs. 2010

[RyanF]

Hi All,

I'm trying to add checkboxes to my worksheet using VBA and stumbled across this code which looks like it'd do the job:
Mr Excel thread from 2002

Pasting this into Excel 2010 and it doesn't seem to like the ActiveSheet.OLEObjects.Add bit (ClassType specified as a Form Checkbox.)

Recording my own macro suggests this should be changed to ActiveSheet.CheckBoxes.Add

Can anyone please confirm if ActiveSheet.OLEObjects.Add works in 2003 and not in 2010?

Ultimately the code needs to work in 2003, just for now I'm working in 2010 until I can get 2003 installed again. the code will add a forms checkbox to a given cell and link it

 

[fredlo2008]

Hello,

I am not sure I actually think OLEObjects are more advanced, one thing I can tell you they offer way more customization.

if you go to the developer tab under insert : regular form checkboxes are under "Form Control" and OLEObjects are under Activex.

Here are two pieces of code once for each checkbox type:

Sub CheckBox()

With ActiveSheet
    'Regular checkbox
    ActiveSheet.CheckBoxes.Add Left:=.Range("C1").Left, _
                               Top:=.Range("C1").Top, _
                               Width:=.Range("C1:D1").Width, _
                               Height:=.Range("C1").Height
  
    'OlEObject
    ActiveSheet.OLEObjects.Add ClassType:="Forms.CheckBox.1", _
                                Left:=.Range("C3").Left, _
                                Top:=.Range("C3").Top, _
                                Width:=.Range("C3:D3").Width, _
                                Height:=.Range("C3").Height
End With


End Sub

Thanks

 

[RyanF]

Hello,

I am not sure I actually think OLEObjects are more advanced, one thing I can tell you they offer way more customization.

if you go to the developer tab under insert : regular form checkboxes are under "Form Control" and OLEObjects are under Activex.

Here are two pieces of code once for each checkbox type:

Sub CheckBox()

With ActiveSheet
    'Regular checkbox
    ActiveSheet.CheckBoxes.Add Left:=.Range("C1").Left, _
                               Top:=.Range("C1").Top, _
                               Width:=.Range("C1:D1").Width, _
                               Height:=.Range("C1").Height
  
    'OlEObject
    ActiveSheet.OLEObjects.Add ClassType:="Forms.CheckBox.1", _
                                Left:=.Range("C3").Left, _
                                Top:=.Range("C3").Top, _
                                Width:=.Range("C3:D3").Width, _
                                Height:=.Range("C3").Height
End With


End Sub

Thanks

Hi Fredlo, thanks for the reply.

Does that mean that objects inserted with OLEObjects.Add are ActiveX Controls? Because I thought ActiveX controls didn't exist in Excel 2003 and prior...

Because of this, and because I only really know Form controls and how they link to cells, I figured I'd be using them.

Using the code from the 2002 thread, I get a Compile Error: Expected: Named Parameter error and it makes this bit red. I'm not familiar enough with it to know why

        Set cb = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _

        Left:=cl.Left + 1, Top:=cl.Top + 1, Width:=cl.Width - 2, _

        Height:=cl.Height - 2)

 

[MARK858]

It is just the spacing between the lines. Try...

Sub Ad_Checks()

    Dim cl As Range, cb As OLEObject

    Application.ScreenUpdating = False

    For Each cl In ActiveSheet.[b2:b800]    'change Range

        Set cb = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                                            Left:=cl.Left + 1, Top:=cl.Top + 1, _
                                            Width:=cl.Width - 2, Height:=cl.Height - 2)

        With cb

            .Placement = xlMove

            .LinkedCell = cl(, 0).Address(False, False)

            With .Object

                .BackColor = &H80000005

                .BackStyle = fmBackStyleTransparent

                .Caption = ""

            End With

        End With

    Next

    Application.ScreenUpdating = True

End Sub

with forms checkboxes

Sub addCheck()
    Dim myCB As CheckBox, c As Range
    Application.ScreenUpdating = False
    With ActiveSheet
        .CheckBoxes.Delete
        For Each c In ActiveSheet.Range("B1:B800")
            With c
                Set myCB = .Parent.CheckBoxes.Add _
                            (Top:=.Top, Width:=.Width, _
                             Left:=.Left, Height:=.Height)
                With myCB
                    .LinkedCell = c.Offset(0, -1).Address
                    .Caption = ""
                End With
            End With
        Next c
    End With
    Application.ScreenUpdating = True
End Sub

 

[fredlo2008]

did you set the range "cl" to something?

This is working in mine

Set cl = Range("C3")

Set cb = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                                Left:=cl.Left + 1, Top:=cl.Top + 1, _
                                Width:=cl.Width - 2, Height:=cl.Height - 2)

And if the workbook is supposed to work for 2003 i would say its better to go with form controls

 

[MARK858]

ActiveX controls are available in Excel 2003 but I would also use the forms version because they are much more reliable

 

[RyanF]

Thanks fredlo, it was as mark suggested - extra line breaks were causing it to not compile. Question answered. Thanks guys