How to define Array of checkboxes in VBA

Question

I know similar questions have been asked before like this and this but I was having issues with initializing the checkbox array object (My VBA is quite rusty).

I have the following code:

Dim chkAdQ(4) As Checkbox

Set chkAdQ(0) = chkAdQ1
Set chkAdQ(1) = chkAdQ2
Set chkAdQ(2) = chkAdQ3
Set chkAdQ(3) = chkAdQ4

where chkAdQ1, chkAdQ2 etc. are ActiveX checkboxes present on the form. On debugging I can see that chkAdQ(4) prompts 'nothing' on the declaration itself and hence the assignment gives a Type mismatch exception.

I also tried by declaring chkAdQ(4) as an Object but to no avail. Any thoughts?

You have not set chkAdQ(4) , so that is nothing.

Dy.Lee
– Dy.Lee

2017-08-25 11:49:33 +00:00
Commented Aug 25, 2017 at 11:49 — Dy.Lee
– Dy.Lee, Commented Aug 25, 2017 at 11:49

Tom · Accepted Answer · 2017-08-25 12:12:38Z

1

You can add all checkboxes on the worksheet quite nicely with a simple loop

Sub AddCheckBoxesToArray()
    Dim chkAdQ As Variant
    Dim cb

    i = 0
    ReDim chkAdQ(i)
    For Each cb In Sheet2.OLEObjects
        If TypeName(cb.Object) = "CheckBox" Then
            If i > 0 Then ReDim Preserve chkAdQ(0 To i)
            Set chkAdQ(i) = cb
            i = i + 1
        End If
    Next cb

    For Each cb In chkAdQ
        Debug.Print cb.Name
    Next cb
End Sub

Remove the second loop when using. This is just to prove that they have all been added by printing their names to the Immediate window

answered Aug 25, 2017 at 12:12

Tom

9,9084 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user7857211 · Accepted Answer · 2017-08-25 11:49:04Z

0

Try this

Dim chkAdQ(0 To 3) As Variant

Set chkAdQ(0) = chkAdQ1
Set chkAdQ(1) = chkAdQ2
Set chkAdQ(2) = chkAdQ3
Set chkAdQ(3) = chkAdQ4

answered Aug 25, 2017 at 11:49

user7857211

1 Comment

hbabbar Over a year ago

of all the things I had to try Variant was the one I missed . Thanks !!

Collectives™ on Stack Overflow

How to define Array of checkboxes in VBA

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related